Color alternate excel rows based on duplicate numbering system.

Hello Experts.

I have a Excel 2003 spreadsheet.  In Column A I have the numbering system below which will go beyond 150 by the end of the year.  I would like to use something like conditional formatting so each new number sequence is an alternate color (grey/white) across to Column AO. (Color in bracket for emphasis only)


1_2013 (white)
2_2013 (Grey)
2_2013 (Grey)
2_2013 (Grey)
2_2013 (Grey)
3_2013 (White)
4_2013 (Grey)
5_2013 (White)
6_2013 (Grey)
7_2013 (White)
7_2013 (White)
8_2013 (Grey)

etc, etc, etc

As soon as the number is entered, it colors that row to the alternate color to Column AO

Thank you in advance.

Matt
mattm2002Asked:
Who is Participating?
 
BenefordConnect With a Mentor Commented:
Is it easy to extract a sequential number from column AO?
eg left(AO1,find("_",AO1)-1)

If so, you can use an even/odd formula in your conditional formatting.

I don't have Excel 2003, but in 2010, I can use the following formulae and have the two formulae covering the same range giving alternate colours as you specified.

=MOD(LEFT($A01,FIND("_",$A01)-1),2)=0
=MOD(LEFT($A01,FIND("_",$A01)-1),2)=1

If it's not easy to get a sequential number, then you could introduce a hidden column that checks AO34 against AO33 as follows:

=IF(A03=A02,B02,IF(B02="White","Grey","White"))

And then use column BO to determine the colour you want.
0
 
mattm2002Author Commented:
Thanks Beneford

I added your sequential suggestion to AP (given AO has data in it)

I did some more research based on your suggestion and found the following worked for 2003, coloring based on an even or odd number.

=AND(MOD($AP2,2)=0,$AP2<>"")
=AND(MOD($AP2,2)=1,$AP2<>"")

Worked like a charm, much appreciated.

Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.