Program a button in Excel to colour cells 1

[protector]

I need to know how to program a button on an Excel spreadsheet to colour a single cell in a column when clicked and then on the next click the next cell is coloured and so on. I have written a macro that does half the job(see below)but I can't figure out how to go to the next cell on the next click. It works if I manually change the cell reference but can this be done automatically.
Private Sub CommandButton1_Click()
Range("G6&quot.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

Hope someone can help
Protector

 

[bjd4jc]

If I am understanding you correctly, You can create a static variable that keeps track of the next row that needs to be highlight.

So it would look something like this:


Static Count As Integer
Dim RangeToColor As String

RangeToColor="G" & RangeToColor + 6
Range(RangeToColor).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Count=Count+1

With Count be a static variable, it will keep it's value until the scope of the command button has been destroyed.

I hope this helps

Brian

 

[protector]

bjdc4jc, thanks for your help, your advice worked great. The code below is what I ended up using. Hope that helps others as well.

Private Sub cmdDataOKYes_Click()
Static count As Integer
Dim cellstring As String

cellstring = "c" & count + 6
Range(cellstring).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
count = count + 1
End Sub