"Green Bar" effect based on duplicate values in a column

[LoriD]

I am assuming I need to do something with Conditional Formtting, but can't figure it out, nor can I find help with this specific request.
Column A holds a "record number". There could be several rows containing the same record number. I want to highlight the row range (A:H), with the same color if the record number in A is the same as the one above it.
Then I simply want this range color to alternate at each change in record number. Is this possible/Can anyone help me do this?
Thanks.

 

[lenze]

See my example here

http://216.92.17.166/board/viewtopic.php?topic=17234&forum=2

 

[Andrew Poulsom]

assuming your list starts in cell A1, select from A2 to the end of your list in column H and choose Format, Conditional Formatting.

Formula is:

=AND($A2=$A1,MOD(ROW($A2),2)=0)

and choose the appropriate pattern.

 

[LoriD]

lenze, Andrew -
Thank you for your replies and taking the time to answer.
Andrew, I'm able to follow your suggestion a little easier. I'm having a problem though, in that it's not highlighting all instances where the result in columnA is the same. ColumnA may have the same record # once, twice, or several times. Since I'm sorry to say I don't understand the formula, I can't figure out what to do to adjust it. Can this be "tweaked" somehow? Lori

 

[Andrew Poulsom]

You said "I want to highlight the row range (A:H), with the same color if the record number in A is the same as the one above it". So that's what my formula does. It compares what is in the current row with what is in the row above, but only for even numbered rows.

What does your data actually look like?

 

[lenze]

Assuming your data starts in row 2, in cell I2 enter
=COUNTIF($A$2:A2,A2)and copy down
In J2 enter =ISODD(COUNTIF($H$2:$h2,1))

Select your range(A:H) and Choose Formatting, Conditional Formatting. use the Formula is option and enter
=$J2=TRUE format your background

Edit: You may hide columns I & J
This message was edited by lenze on 2002-10-11 11:31

 

[LoriD]

In columnA, rows 2 through 5 contain "0001"
Only row 4 is highlighted

ColA, row 6 = "0002". This is not highlighted.

ColA, row7 = "0003". Not highlighted.

ColA, rows through 10 = "0004". Only row 10 is highlighted.

Is this what you need to know?

 

[LoriD]

I got it this time lenze, Thank You!!!

 

[Andrew Poulsom]

If your data starts in row 2 you need to modify my formula to colour odd rows instead of even rows. So in A3:H wherever:

=AND($A3=$A2,MOD(ROW($A3),2)<>0)