Repainting a cell using excel VBA

[Closed]
Report
-
 Helper -
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

2 replies

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
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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

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