Conditional Formatting Shading For Visible Rows Only

[manda]

I am using conditional formatting formula =MOD(Row(),2)=0 to shade
alternate rows, but when I hide a row the shading format is not
consistent. Is there way to shade alternate 'Visible Only' rows without
creating a macro?

 

[tony h]

you can do it using a user-defined-function. The UDF is then used on th
worksheet.
Would that class as a macro for this purpose?

regard

 

[manda]

The UDF seems a little too complex for me. Is there maybe some kind of
'Visible' code that I could use for the conditional formatting formula?

 

[tony h]

this function returns a true or false depending on the number of visibl
rows

Function visLines(ByRef rng As Range) As Boolean
Application.Volatile
Dim rng1 As Range
Dim rng2 As Range
Dim bln As Boolean

Set rng1 = rng.Resize(rng.Row).Offset(1 - 1 * rng.Row)

For Each rng2 In rng1
If rng2.Height <> 0 Then
bln = Not bln
End If
Next
Debug.Print rng1.Address, l, bln
visLines = bln
End Function


then use conditional formatting using the cell reference so if in cel
D5 use vilines(D5)

cheer