Stepping thru a range in a sub

[JenniferMurphy]

I need to step thru a range of cells in a Sub setting the fill to either red or green depending on whether the contents are less than or greater than 0.

Using the macro recorder and information from MSDN, I came up with this:

Const Col1 As Long = 3
Const Col2 As Long = 6
Const Row As Long = 22
Dim Col As Long
For Col = Col1 To Col2
  Range(.Cells(Row, Col), .Cells(Row, Col)).Select
  With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    If .Value > 0 Then
      .color = 65280
    Else
      .color = 255
    End If
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
Next Col

It fails on the Range statement with "Invalid or unqualified reference".

If I change the Range statement to

Range("C22:F22").Select

Then it fails on the If .value statement with "Object doesn't support this property or method".

Can someone help me with the correct syntax to get this to work?

 

[Joe4]

To loop through all the cells in a range, you can do it like this:

Dim cell as Range
For each cell in Range("C22:F22")
    ...
Next cell

However, it seems like you could do what you want with Conditional Formatting. Is there a reason that you do not want to use it here?

 

[JenniferMurphy]

To loop through all the cells in a range, you can do it like this:

Dim cell as Range
For each cell in Range("C22:F22")
    ...
Next cell

OK, but how do I reference each cell inside the loop? I need to test each cell to see if it's greater or less than zero and then set the fill color accordingly. Can you provide the syntax for the test and then the color assignment?

However, it seems like you could do what you want with Conditional Formatting. Is there a reason that you do not want to use it here?

I was using conditional formatting, but now I need to copy the colored line to create a history. But when the conditions change, the rules no longer work properly. I couldn't find a way to copy the colors without the rules, so now I'm going to apply the colors in a macro so they can be copied.

 

[Joe4]

OK, but how do I reference each cell inside the loop?

With the name of the range variable we chose ("cell"), i.e.

If cell.Value > 0 Then

 

[JenniferMurphy]

With the name of the range variable we chose ("cell"), i.e.

If cell.Value > 0 Then

This seems to work:

Dim cell As Range
For Each cell In Range("I3:AA3")
  Select Case cell.Value
    Case Is > 0: cell.Interior.color = 65280
    Case Is < 0: cell.Interior.color = 255
  End Select
Next cell

Any comments?

 

[Joe4]

Looks good!