making certain text values go colour red

[Graham]

Hi all


In column (I) is numbers, In column (J) is text values with duplicates. What
I'd like to happen is if the next same text value working down the column as
a number 1 in the cell to the left of it (J) is make the text cell above
colour red. and apply this to the sheet and to work when new rows are added.

example

I1 = 1 J1 = John <------- This text would not be red
I2 = 2 J2 = David
I3 = 3 J3 = Albert <------ This text would be red because I5 = 1
I4 = 9 J4 = John
I5 = 1 J5 = Albert
I6 = 6 J6 = John

My data is from I1 to J2130

Thanks in advance

Graham

 

[Juan Sanchez]

Graham

go to cell J1 and the go to Conditional Formmating, on the
Format Menu,

Paste this formula on the "Formula Is" box (you have to
change form "Cell Value Is" to "Formula Is" on the drop
down list).

=INDIRECT(ADDRESS(ROW()+MATCH(J1,J2:$J$2130,0),COLUMN()-
1,4))=1

Then click on the Format Button and format as you want
cells to appear when condition is true i.e. Red + Bold

Then click OK.

Copy cell J1 and the select the entire range j1:j2130 and
paste special > formats only...

Take a look at Excel's Help for conditional formatting.


Cheers
Juan