Dynamic Color Banding on similar rows with Filtering

Matthew Recknagel

New Member
Joined
Nov 2, 2011
Messages
13
Hello Mr. Excel,

I have been searching on multiple occasions for a solution to my highlighting needs. At one point and time I found a guide that showed me how to highlight all rows that had the same cell value, then when the cell value changed it would alternate the shading. It used a MOD formula that was inserted into the first column and returned a 0 or 1 as pictured below, then with conditional formatting you could highlight based off of the 1 or 0. This worked great until I wanted to filter the data, lets say odds only, it would filter but keep the original Mod formula value resulting in the table to not be alternate shading any more. (could not highlight so i used bolded text to show what i mean)
Unfiltered Table
Filtered Table (odds)
Mod Formula
Sorted Value
Data
Mod Formula
Sorted Value
Data
0
1
10
0
1
10
0
1
10
0
1
10
1
2
35
0
3
6
0
3
6
0
3
7
0
3
7
1
4
5
1
4
5
1
4
10

<tbody>
</tbody>

My question would be, is there any conditional formatting setup that could alternatively shade based on cell value AND work with filtering? I have seen many guides that alternate shading every 2 or 3 rows which is not what i am looking for. Any help?

Thank You,
Matt Recknagel
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,568
Members
414,079
Latest member
Frills

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
Top