A few words of thanks would be greatly appreciated.

Repainting a cell using excel VBA

Latest update on November 4, 2009 at 12:56 AM by aakai1056 .

Issue
Solution
Note

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.

Excel vba repaint
Me.repaint vba - Best answers
Vba me.repaint - Best answers
Cells.find excel vba - How-To - Excel
Find blank cells in excel vba ✓ - Forum - Programming
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

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).

Excel - Send value of cell to target

Excel - Changing cell formula to text

Issue

Solution

Note

Subscribe To Our Newsletter!