Alternate cell interior color in range (some rows hidden) 2

[RP1America]

I need to alternate the interior color of B7:C41.

B7:C7 will never be hidden and should be RGB(219, 229, 241). The next unhidden row should be B?:C? = RGB(184, 204, 228). These colors should alternate through B41:C41 (which will never be hidden).

I'm having trouble coming up with the loop logic. Any thoughts?

Thanks!

 

[SkipVought]

why not use conditional formatting using =mod(row(),2)=0

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks, Skip! Looking into it now and it seems to be exactly what I was looking for.

THANKS!

 

[RP1America]

Please correct me if I am wrong, but this doesn't seem to take into account the hidden rows.

 

[SkipVought]

Why should that matter?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Because I have hidden rows on my sheet.

If
Row 1 = Visible
Row 2 = Hidden
Row 3 = Visible

Then rows 1 and 3 will be visually adjacent and their interior colors would be identical. This is what I don't want. I want every other visible row to be a different color.

 

[SkipVought]

Then you might have to run VBA, incriment your mod counter when the row is visible.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

How would I do that?

I found this:

    Dim Counter As Integer
    For Counter = 1 To Selection.Rows.Count
        If Counter Mod 2 = 1 Then
            Selection.Rows(Counter).Interior.Color = RGB(219, 229, 241)
        ElseIf Counter Mod 2 = 2 Then
            Selection.Rows(Counter).Interior.Color = RGB(184, 204, 228)
        End If
    Next

But it still obviously only works for even and odd row numbers.

Can this be simply modified for what I need?

If not, would you mind providing a different example? Not sure where to start on this one.

 

[SkipVought]

Instead of Counter in a For...Next loop, use a Do loop & incriment Counter on Visible rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?

Dim Counter As Long, i As Long
For Counter = 1 To Selection.Rows.Count
    If Selection.Rows(Counter).EntireRow.Visible Then
        i = i + 1
        If i Mod 2 = 1 Then
            Selection.Rows(Counter).Interior.Color = RGB(219, 229, 241)
        Else
            Selection.Rows(Counter).Interior.Color = RGB(184, 204, 228)
        End If
    End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:

Dim Counter As Long, i As Long
For Counter = 1 To Selection.Rows.Count
    [!]If Not Selection.Rows(Counter).Hidden Then[/!]
        i = i + 1
        If i Mod 2 = 1 Then
            Selection.Rows(Counter).Interior.Color = RGB(219, 229, 241)
        Else
            Selection.Rows(Counter).Interior.Color = RGB(184, 204, 228)
        End If
    End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RP1America]

That did it PH! Thanks for the help!

 

[Deniall]

Incidentally, RP1America, in your 10Feb12@16:57 post your ElseIf condition can never be True.