Macro that runs on entire column - not stopping on blanks

[dadel]

Hi,
I've got a column with some blanks in it, which I want to run a macro on.
How do I tell it to run on the entire column, all the way to the last value, without stopping on the first blank cell?

Here's my macro.

Public Sub MyMacro()
Dim rngCell As Range
Set ws2 = Workbooks("a.xls").Sheets("Sheet2")
For Each rngCell In ws2.Range(Cells(2, "C"), ws2.Cells(Rows.Count, "C").End(xlUp)).Cells
If rngCell.Offset(, 1).Value = "A" Then
With rngCell
.Value = "AAA"
End With
End If
Next rngCell
End Sub

[royUK]

Is your Coulmn C? If so your code should not be affected by blanks, this works

Please Login or Register  to view this content.

[dadel]

Is your Coulmn C?

yep

If so your code should not be affected by blanks, this works

Oh, you're right. It did! (see now that i had another problem with my code that was causing it to malfunction)

However, I have a different variant of this macro, that doesn't seem to work.
The only difference is that it checks for a color instead of a value.
But for some reason, this does not work. It stops on the first blank cell in C.

Public Sub MyMacro()
Dim rngCell As Range
Set ws2 = Workbooks("io.xls").Sheets("IO-List")
For Each rngCell In ws2.Range(Cells(2, "C"), ws2.Cells(Rows.Count, "C").End(xlUp)).Cells
If rngCell.Offset(, 1).Interior.ColorIndex = 15 Then
With rngCell
.Value = "AAA"
End With
End If
Next rngCell
End Sub

[royUK]

Your code works so either the colour is not 15 or there is another problem.

Also, you do not declare ws2 as a variable. Again this code is neater

Please Login or Register  to view this content.

[dadel]

OK. Just found out that the problem is that that I'm using Conditional Formatting to color the cells.

And for some reason coloring with conditional formatting does not change the interior color index of a cell.

How annoying.

Also, you do not declare ws2 as a variable. Again this code is neater

thanks for the tip.