[SOLVED] Conditional formatting, alternate row colour to promote easy reading of long list

[rmg08057]

Hi there

I have a long list of invoice lines. It is multiple columns. First two columns are Invoice # and Invoice line. Neither the invoice # nor the invoice line # is necessarily consecutive in the list. The list may look like:
Invoice # Invoice Line # InvDate Qty etc etc
12345 1 10/10/2010 1000
12345 3 10/10/2010 46
17010 2 17/10/2010 100
10001 4 09/10/2010 9
10001 5 09/10/2010
10001 7 09/10/2010

I would like to highlight the rows to promote easier reading/skimming, with alternate (say) blue and white on the entire row. In the above example this would mean the 2 lines for Invoice 12345 would be white, the single line for Inv 17010 would be blue, the three lines for Inv 10001 would be white etc etc. Banded highlighting, breaking on invoice #, alternating between two colours.

Is this possible?

TIA

Ralph in Auckland

[FDibbins]

use conditional formatting.

high-light your entire range. when you open the CF window, select "use formula", and copy in this formula

=OR($A2=$A3,$A2=$A1) - select blue as the format color. all alternate entries will be white/blue

[rmg08057]

Thanks... this kinda worked, but produced inconsistent results, about 80% it got it right, but then for some it would highlight a set of rows that had different numbers... strange. Any thoughts? I have attached an example of it not working.Example conditional formatting.xlsx

[FDibbins]

ok i see where it went wrong. try this instead...i added a helper column in T and copied this formula down...
=MOD(IF($A2=$A1,$T1,$T1+1),2)=1

then i used this formula in CF...
=$T2

[rmg08057]

Brilliant, works perfectly. This opens a world of possibilities, using TRUE/FALSE conditions. Hey, I am away.

Sorry not to reply sooner, been head down on project, too busy working on pages of invoice lines to make it easier for me to look at pages of invoice lines.

Spent 10 years in Philly, never went past your place unfortunately, mostly heading east/west in the turnpike south of you.

Ralph

[FDibbins]

glad to help, and thanks for the star