Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

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
0
mattm2002
Asked:
mattm2002
1 Solution
 
BenefordCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now