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

#### LoriD

##### Board Regular
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.

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

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?

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

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?

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

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)

Replies
20
Views
564
Replies
1
Views
160
Replies
6
Views
135
Replies
10
Views
265
Replies
1
Views
84

Threads
1,203,070
Messages
6,053,365
Members
444,657
Latest member
jessejames1of3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back