Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Color alternate excel rows based on duplicate numbering system.

Posted on 2013-01-29
Medium Priority
486 Views
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

Matt
0
Question by:mattm2002
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 7

Accepted Solution

Beneford earned 1500 total points
ID: 38830175
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

Author Comment

ID: 38830240
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month8 days, left to enroll