Shade every other set of rows with unique text string

**cmaunder** · 07-05-2013, 12:05 PM

Hello,

I'm hoping this is as simple to solve as it is to ask. I have a workbook in which shading is used to differentiate between rows representing different locations (unique location name/number combos). Every other location is shaded. When new locations are added, it throws the shading off.

This is a real pain to adjust, even using filtering by colour, and filtering doesn't help when creating a shaded workbook from scratch. I'm hoping someone knows how to code a workaround that would do this automatically.

I've attached an example workbook.

Thanks very much for your help!Macro Documentation - Add-Change Rows Example.xlsm

**JBeaucaire** · 07-06-2013, 03:45 AM

1) Remove the manual shading from the table so nothing is shaded

2) Let's keep from trying to use a Conditional Formatting that will bog your sheet down since your data set is so large. Put this formula in J10, then copy down the whole dataset.

=IF(A10=A9, J9, J9+1)

3) Highlight A10:I1089.

4) Apply this new Conditional Formatting formula rule:
=MOD($J10,2)=0

...and select Format..Fill...grey color of your choice.