Want conditional formatting to reset after column filters are used

[reasem]

Using the formula=MOD(ROW(),2)=1 to have every other row have color to make it easier to use a large spreadsheet I have. When I change the column filters the rows are no longer colored every other row obviously. Can I alter the formula at all to have it reset when filters are added? somehow use Visible cells maybe?

Thanks

 

[JoeMo]

Don't know if that can be done using CF. Here's a VBA alternative that I use. I keep this macro in my Personal.xlsb workbook and assign a button on the Quick Access Toolbar to it. Filter the data, then select the visible range you want to color band and click the button to produce the banding. After the filter is removed, select the visible range again and click the button to band the unfiltered rows.

Sub ColorBandAltRowsEvenHidden()
Dim R As Range, Ar As Range, Rw As Range, i As Long
Set R = Selection  'works on user-selected range
Application.ScreenUpdating = False
R.Interior.Color = xlNone
On Error Resume Next
For Each Ar In R.SpecialCells(xlCellTypeVisible).Areas
       For Each Rw In Ar.Rows
              ct = ct + 1
              If ct Mod 2 = 0 Then
                     Rw.Interior.Color = RGB(215, 215, 215) 'Change fill to suit
              Else
                     Rw.Interior.Color = xlNone
              End If
       Next Rw
Next Ar
On Error GoTo 0
End Sub

 

[Fluff]

Another option
On the Formula Tab select Name Manager > New > enter a name (I called it IsVisible) > in refers to put this formula
=GET.CELL(17,INDIRECT("rc",FALSE))
OK > Close
In a blank column put
=IsVisible (or whatever name you used)

Then select your data & in CF use
=ISEVEN(COUNTIF($AF$2:$AF2,">0"))

Change AF to whatever column holds the IsVisible formula
The workbook will need to be saved as macro enabled.