Colour coding blocks of rows using conditional formatting

My macros have been taken away; never to be used again! I'm gutted. I now need to use formatting to help me highlight the different users and their data. Doing this manually is a chore.

Using Excel 2007, is there any way to say 'alternate colour between Grey and White based on the matching names in row A'? Row A is sorted so that names are in order and together.

Bob (White)
Bob (White)
Derek (Grey)
Jane (White)
Jane (White)
Jane (White)
Jane (White)
etc (Grey)
etc (etc)

RocketGoal

Posted 2010-01-04T14:47:27.863

Reputation: 1 468

Why can't you use macros? I will say that conditional formatting changed a lot in 2007, so your macros may need to change, but they should still be there. – DaveParillo – 2010-01-04T15:17:26.613

The system now prohibits macros. – RocketGoal – 2010-01-04T15:40:43.137

Answers

Edit: completely revised my answer. This one required a "helper column" but seems to work much better.

From David McRitchie's Excel Pages site on conditional formatting:

We want to Group anytime there is a Change in Column A or Column B. Conditional Formatting can not keep track of what the previous color was, so we will have to use a helper column, and then have Conditional Formatting check the helper column.

The basic formula for the required helper column is:

E1:  0 
E2: =MOD(OFFSET($E2,-1,0)+ OR($A2<>OFFSET($A2,-1,0),$B2<>OFFSET($B2,-1,0)),2)

From E2, just drag the formula down as far as you need to with your data.

Your conditional formatting formula will be:

=$E1=1

Visit the conditional formatting in groups page for an explanation of how and why this works.

Jared Harley

Posted 2010-01-04T14:47:27.863

Reputation: 11 692

This doesn't work for me. I've selected the whole table of data and followed your steps exactly. No banding of rows occurred. – RocketGoal – 2010-01-04T15:34:41.820

Admittedly, I found this solution on another website - I've been playing with the formula, and it seems to be rather specific in how it applies to the data. The name data needs to be in the A column, and it has to start in A1. I was able to insert a single row above A1 once the formatting was applied, but a second borked the coloring. I'll keep playing around and see what I can figure out. – Jared Harley – 2010-01-04T15:48:21.997

Apologies. I thought I followed your steps exactly. I went back and found I had done $A$1:$A$76 - the whole range. I've since changed it to your but it's quite sporadic in it's banding. I'm trying different things, but any idea why it would band sporadically gratefully appreciated. – RocketGoal – 2010-01-04T15:49:10.933

I updated the formula with some AND logic so that Excel will color new rows as you add them. Still trying to get the code to be less weird... – Jared Harley – 2010-01-04T16:00:52.173

Just not happening. Followed the steps and have this strange banding. It starts off correctly for three groups, and then begins too band half way down the fourth group. And after that begins at the top of the next group but only for half of it and then stops banding altogether. – RocketGoal – 2010-01-04T16:04:11.387

Okay. After doing some more reading, it looks like my first answer was extremely problematic (as we both figured out). Check out the new answer, which works so far with all of my testing. – Jared Harley – 2010-01-04T16:27:00.343

Works like a charm. Many thanks for you help and persistence. Great Website you've linked to as well. – RocketGoal – 2010-01-05T11:10:09.403

Assuming your data start on row 2 (i.e. row 1 is a header) and column A is the 'data group' headings (your Bob's and Dereks above), try this:

Set up an additional column (which you'll hide later) and put this formula in row 2:

=IF(I1=1,(IF(A2=A1,1,0)),(IF(A2=A1,0,1)))

Drag this formula all the way to the bottom of your data set. You should now have a tidy set of 1's and 0's which match your data blocks. (1's for first block, 0's for second block, 1's for third block and so on)

Now setup a simple conditional format against row I:

Formula: =$I5=1
Format: set format to gray
Applies to =$A$2:$G$100

As long as you keep row I outside of the autofilter selection, it'll even work dynamically as you sort your data (using autofilter).

Gavin

Posted 2010-01-04T14:47:27.863

Reputation: 21

If you want to do this without a helper column, you can use this as your CF formula:

=MOD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1),2)=0

Just select your formatting, and you're done.

Greg G

Posted 2010-01-04T14:47:27.863

Reputation: 11

I don't understand it, but this did exactly what I wanted as far as I can tell. – jmoreno – 2016-01-22T00:30:26.137

An easier-to-understand way to do it would be to fill a helper column by putting the formula

=IF(A3=A2,B2,ABS(B2-1))

in a helper column (column B). You'd have to just put a zero in B2 (I'm assuming you have header row), paste this in B3 and then drag it down.

You then use the conditional formatting formula:

=$B2=1

MattPage

Posted 2010-01-04T14:47:27.863

Reputation: 1