Format Records alternating colours.

[witharebelyell]

Hi

I would like to have aternating rows differnet colours.

eg.

customer no
123 red
123 red
456 green
456 green
456 green
789 red
789 red

Can you tell excel to do this?

mike

 

[Ron Coderre]

Try this:

Insert a blank (helper) column before your data.
(I'll assume it will be Col_A. Col_B will list customers)

A2: =IF(B2=B1,A1,NOT(A1))
Copy A2 down as far as you need

Select from B2 down and to the right as far as you have data
...with B2 as the active cell.

From the Excel Main Menu:
<format><conditional formatting>
Condition_1:
Formula is: =$A2
Click the [Format] button
....select the Red color you want
....Click [OK]
Click the [ADD] button
Condition_1:
Formula is: =NOT($A2)
Click the [Format] button
....select the Green color you want
....Click [OK]
....Click [OK]

Now....
The first customer's data will be Red.
The second's will be Green.
The third's will be Red
etc

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

 

[T. Valko]

One way:

Assume your data is in the range A2:A8

You need a helper column so I'll use column B. You can hide column B
afterwards so it's not showing.

Enter an x in B2.
Enter this formula in B3 and copy down to B8:

=IF(A3=A2,B2,IF(B2="x","y","x"))

Now, set the color bands
Select the range A2:A8
Goto Format>Conditional Formatting
Condition 1
Formula Is: =B2="x"
Click the Format button
Select the Patterns tab
Select a shade of RED
OK
Click the Add button
Condition 2
Formula Is: =B2="y"
Repeat the above process for a shade of GREEN
OK out

Hide column B if desired

 

[witharebelyell]

Thanks

Ron & Co

I tried method 1 (Rons) eventually go it to work.

THANKS

btw one question 1 ,some times when i export a Access report to
excel - i notice that excel has the grouping with + and minus , tree
in it ? ie it has the reports breaks in excel, how do you do this
grouping in an excel spread sheet from the get go..???

its friday after noon here in oz , so i might be going soon!

hav a good weekend