Want conditional formatting to reset after column filters are used

reasem

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top