VBA to cycle through range

[rjplante]

I have a range of cells that spans a block of columns, but there is a blank column in between each of the columns of data. I want to start at the top left most cell and have it go down to the bottom of that column and the over to the right to the next block of cells 2 columns over. Then down and over until it has reached the last cell in the block. The goal is to find the first cell that does not have any color in it and then apply the desired color to that cell and then exit the sub. My code is listed below and when I run it, it moves to the first cell in the range and then displays "Done" indicating that the macro has run, but nothing happens. No cells had their interior color changed. What am I doing wrong and how do I fix it?

Sub COLOR_ME()

Dim Cell As Range

For Each Cell In Range("AC4:AC7,AE4:AE7,AG4:AG7,AI4:AI7,AK4:AK7")
    If Cell.Interior.Color = xlNone Then
        ActiveCell.Interior.Color = RGB(75, 205, 255)
        Exit Sub
    End if
Next Cell

MsgBox "Done"

End Sub

 

[Joe4]

You need to change this line:

ActiveCell.Interior.Color = RGB(75, 205, 255)

to this:

Cell.Interior.Color = RGB(75, 205, 255)

as the ActiveCell never changes in your code, as you are not selecting any cells.

 

[rjplante]

Thanks for that change. I have made the correction, but there is still no change in any of the cells when I run the macro. No color has been added. Any other thoughts?

 

[Joe4]

Thanks for that change. I have made the correction, but there is still no change in any of the cells when I run the macro. No color has been added. Any other thoughts?

On testing, it also looks like this:

    If Cell.Interior.Color = xlNone Then

needs to change to this:

    If Cell.Interior.Pattern = xlNone Then

So your final code should look like:

Sub COLOR_ME()

Dim Cell As Range

For Each Cell In Range("AC4:AC7,AE4:AE7,AG4:AG7,AI4:AI7,AK4:AK7")
    If Cell.Interior.Pattern = xlNone Then
        Cell.Interior.Color = RGB(75, 205, 255)
        Exit Sub
    End If
Next Cell

MsgBox "Done"

End Sub

 

[rjplante]

That did it!! Thanks for the interior color education. I appreciate your quick response.

 

[Joe4]

You are welcome.
Glad I was able to help!

When in doubt, just turn on the Macro Recorder and record yourself adding and removing color from cells, and that helps you see what Excel is expecting.

 

[rjplante]

Forgot about that simple solution option. Thanks again.