5

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)
2
  • 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. Jan 4, 2010 at 15:17
  • The system now prohibits macros.
    – RocketGoal
    Jan 4, 2010 at 15:40

4 Answers 4

3

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.

7
  • 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
    Jan 4, 2010 at 15:34
  • 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. Jan 4, 2010 at 15:48
  • 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
    Jan 4, 2010 at 15:49
  • 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... Jan 4, 2010 at 16:00
  • 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
    Jan 4, 2010 at 16:04
2

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).

1

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.

1
  • I don't understand it, but this did exactly what I wanted as far as I can tell.
    – jmoreno
    Jan 22, 2016 at 0:30
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .