Want conditional formatting to reset after column filters are used

reasem

New Member
Joined
Nov 15, 2019
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
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.
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,246
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top