Conditional Formatting Issue While Grouping

STCrosstown

New Member
Joined
Aug 24, 2018
Messages
9
I am using the following formula to apply a fill color to every other line on a worksheet.

=MOD(ROW(),2)=0

I am also trying to group rows to condense the sheet for a summary presentation. Problem is, when I group the rows the formatting does not adjust so I get numerous rows of shaded or non shaded ones next to each other.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
How are you grouping the rows? That cf formula should not be affected.
 
Upvote 0
Well, I can't say I am familiar with the grouping tool, but I tried it and I don't think I got the results you're describing. It has the option of collapsing the grouped rows... maybe that is skewing the cf highlighting?
 
Upvote 0
Welcome to the MrExcel board!

See if this work-around is of use to you. It requires Excel 2010 or later.
For my sample, the first row of the first group is row 4. You will have to adjust the instructions below if starting at a different row.

In a vacant column (I have used column J) Fill from row 4 (or row 1 it wouldn't matter) down at least as far as the bottom of your last group (doesn't matter if it goes further) with the number 1
You can later hide that helper column if this method works for you.
Select the range to be banded (for me A4:C26)
Apply this Conditional Formatting formula rule: =MOD(AGGREGATE(2,1,$J$4:$J4),2)=0
Test
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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