Colour conditional formatting alternating blocks of rows

**Deanomcbeano** · 10-03-2012, 08:42 AM

Afternoon all

How do I set up an automatic colour conditional formatting for alternating blocks of rows? At present I manually colour format to highlight rows of data so that one weeks data (typically anything between 0-4) rows is different from the previous. I have a column that ascribes a number to a week of the year, but I cannot use this to do for example Pink=odds, and Yellow=evens because some weeks might not have any data (and therefore have no rows), and I am more concerned with there being a contrast between the weeks that do show.

I've attached the spreadsheet as it is so you can see how I am currently doing the manual formatting.

Any suggestions?

Dean

**NBVC** · 10-03-2012, 09:30 AM

You can use the method found here: http://www.cpearson.com/excel/ContentBanding.aspx

So you place a TRUE in Y4 and in Y5 enter:

=IF(H5=H4,Y4,NOT(Y4))

copied down

Then format the rows based on that True/false value

ie. you select A5:X246 and go to Format|Conditional Formatting.. select cell value Is and select Equal to and enter =$Y5

then click format and choose from pattern tab.

Then click Ok and click Add then repeat above with Cell Value is >> Not Equal to and enter :$Y5

Note: Row 4 should be removed before hand or it should be hard coloured because it will be opposite of row 5

**Deanomcbeano** · 10-03-2012, 10:48 AM

Thanks for the reply, that's helpful. There is one problem. When I input a new entry, I insert a line above row 5, (which then becomes the new row 5). This line isn't covered by the conditional formatting that I have set (which previously covered A5:X246 and changes to A6:X247). How can I fix the conditional formatted area to cover A5:X1000 no matter what rows I input?

Cheers
Dean

**pinball4530** · 10-03-2012, 11:21 AM

Select all cells in the spreadsheet
Navigate to conditional formating, based on equation
Enter =IF(MOD(ROW(A1),2)=0,TRUE,FALSE)
Select the color

(This makes all even rows a color)

**Deanomcbeano** · 10-03-2012, 01:09 PM

Hi

Unfortunately the last post does not achieve what I would like. As per my previous comment is there a way to 'fix' the area of conditional formatting so that it does not change, even if additional rows are added? Even if these rows are added at the top of the formatted area, rather than at the bottom. The area to be fixed is A5:X1000.

Any suggestions?

Thank you

Dean

**NBVC** · 10-03-2012, 01:29 PM

How about if you put the TRUE in Y3

Then in Y4:

=IF(H4=H3,Y3,NOT(Y3))

copied down.

It looks like when you insert new row at the top, you will need to copy other formulas up anyway, so copy this helper one up too...

**Deanomcbeano** · 10-03-2012, 01:39 PM

Hi there, this still doesn't copy up the conditional formatting unfortunately. I'm beginning to think I might be worth recording the sequence as a macro as I can't see a way round. Unless anyone has any more suggestions?

**NBVC** · 10-03-2012, 01:48 PM

If you selected from A4 down to format, and you insert between row 4 and row 5, and copy the Y5 formula up, then the conditional formatting should work

the CF rules are

=$Y4=TRUE

and

=$Y4=FALSE

see attached, I inserted a line above 5 (and below 4) and entered 40 in H4 to get the new colouring... insert another row above the current row 5...