Search : in
By :

Repainting a cell using excel VBA

Last answer on Feb 13, 2009 12:48:47 am GMT Vish, on Nov 25, 2008 9:15:21 am GMT 
 Report this message to moderators

Hello,
I am writing a code in excel VBA to generate a report.I am totally new to VBA.Here's my code

Private Sub CommandButton1_Click()
Dim a1 As Integer
Dim d1 As Integer
Dim r1 As Integer
a1 = Sheet1.Cells(2, 3)

d1 = a1 \ 8
r1 = a1 Mod 8

If d1 >= 5 Then

Sheet1.Cells.Range("A20:E20").Interior.Color = vbBlack

Else

For cnt = 1 To d1

Sheet1.Cells(20, cnt).Interior.Color = vbBlack

Next cnt

If r1 <> 0 Then

Sheet1.Cells(20, cnt).Interior.Color = vbBlack

Sheet1.Cells(20, cnt).Font.Color = vbWhite

Sheet1.Cells(20, cnt) = (8 - r1)

End If

End If
End Sub
..
The basic idea is to colour cells after the click of command button based on the value in Cells(2,3).Cells get coloured based on multiples of 8. The problem that I am facing is that the cells retain the colour permanently.
For example based on the value in Cells(2,3) if 5 cells get coloured ,they remain there permanently.When I change the value in cells(2,3) and click the command button repainting of cells does not take place.. Can anyone please guide me regarding this??

Thanks

Configuration: Windows 2000
Internet Explorer 6.0

Best answers for « Repainting a cell using excel VBA » in :
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Excel - Send value of cell to target ShowExcel - Send value of cell to target Issue Solution Note Issue Is it possible to send the value of a cell (copy and paste special) to another cell where the value would not be disturbed when the source value changes to '0'? My...
Download XLS (Excel) to DBF Converter ShowDescription The application is designed by WhiteTown Software. XLS (Excel) to DBF Converter is a tool that enables you to convert your XLS files to DBF format quickly and easily. Simple and easy to use, the application has been awarded from various...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

Greebo13, on Feb 12, 2009 10:57:34 am GMT
  • +2

Try adding this to the start of it, should clear everything out before you begin again

Range("Sheet1!A2:H65536").ClearContents

   
Reply to Greebo13

2

 Helper, on Feb 13, 2009 12:48:47 am GMT
  • +4

Try this at the beginning of the code. This will clear all cells in the worksheet that are colored.

Cells.Select
Selection.Interior.ColorIndex = xlNone
Cells(2, 3).Select




Private Sub CommandButton2_Click()

Dim a1 As Integer
Dim d1 As Integer
Dim r1 As Integer


Cells.Select
Selection.Interior.ColorIndex = xlNone
Cells(2, 3).Select

a1 = Sheet1.Cells(2, 3)

d1 = a1 \ 8
r1 = a1 Mod 8

If d1 >= 5 Then

Sheet1.Cells.Range("A20:E20").Interior.Color = vbBlack

Else

For cnt = 1 To d1

Sheet1.Cells(20, cnt).Interior.Color = vbBlack

Next cnt

If r1 <> 0 Then

Sheet1.Cells(20, cnt).Interior.Color = vbBlack

Sheet1.Cells(20, cnt).Font.Color = vbWhite

Sheet1.Cells(20, cnt) = (8 - r1)

End If

End If

End Sub

   
Reply to Helper
Ask your question Reply
They need your help