Repainting a cell using excel VBA
Issue
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 color cells after the click of command button based on the value in Cells(2,3).Cells get colored based on multiples of 8. The problem that I am facing is that the cells retain the color permanently.
For example based on the value in Cells(2,3) if 5 cells get colored ,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??
Solution
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
Note
Thanks to Helper for this tip on the forum.
Related
- Excel vba repaint
- Me.repaint vba - Best answers
- Vba me.repaint - Best answers
- Cells.find excel vba - How-To - Excel
- Select non blank cells in excel vba ✓ - Forum - Excel
- Disable cells in excel vba ✓ - Forum - Excel
- Excel vba create new sheet with name from cell - How-To - Excel
- Excel vba change cell color based on value ✓ - Forum - Programming
This document, titled « Repainting a cell using excel VBA », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
Subscribe To Our Newsletter!
