Find exact value and change font colour

**ExcelecxE** · 06-27-2007, 05:44 AM

hi guys, i m new to this forum.

Currently I am working on a project to analysis the production process of a factory. How to make excel find the same value and change the font colour of both cell?

e.g.

colum A contains a list of item code. (yh1234-kw)
if an exact item code being input in the same colum (A), both cell contains the item code will change its colour to red (i mean the font colour).

Anyone know how to do it? I am guessing it could be done by VB.. but i have no idea about VB

Thx in advance

**oldchippy** · 06-27-2007, 06:52 AM

Try this with Conditional formatting,

Select your range of data in column A, go to Format > Conditional formatting, select Formula is and insert

=COUNTIF($A$1:$A$100,A1)<>1 then format your font to RED or whatever.

This will hightlight any duplicates in the same column. Adjust the range to suit your range.

**ExcelecxE** · 06-28-2007, 05:47 AM

Thanks for thr reply. But it doesnt work on my worksheet..
It's more like randomly turns the data into red. But not turn all duplicates into red.

**oldchippy** · 06-28-2007, 07:39 AM

Try this, see if this works

**ExcelecxE** · 06-29-2007, 02:44 AM

your attached file works perfect. However when i try to apply the formula to my worksheet, it doesnt work perfectly.

I attached the worksheet I am working on. I input the conditional formatting for colum C. Can you please check what I've done wrong?

**vikas.bhandari** · 06-29-2007, 03:57 AM

Originally Posted by ExcelecxE

your attached file works perfect. However when i try to apply the formula to my worksheet, it doesnt work perfectly.

I attached the worksheet I am working on. I input the conditional formatting for colum C. Can you please check what I've done wrong?

You have mentioned the formula as =COUNTIF($C$1:$C$1000,C65536)<>1

please change it to =COUNTIF($C$1:$C$1000,C1)<>1

It will correspond the c1 to c1 and c2 with c2 and so on and will give the correct values.

Vikas B

**oldchippy** · 06-29-2007, 04:07 AM

Originally Posted by ExcelecxE

your attached file works perfect. However when i try to apply the formula to my worksheet, it doesnt work perfectly.

I attached the worksheet I am working on. I input the conditional formatting for colum C. Can you please check what I've done wrong?

The problem here is that you have headers at the top, so you will need to adjust the formulas accordingly. But first, cell A1 is a merged cell, remove CF from that cell. You have also set CF to column A from A4:A1000, do you want it on column A?

If so select those cells and change the formula to

=COUNTIF($A$4:$A$1000,A4)<>1

The same applies to column C, so change those to C4:C1000

=COUNTIF($C$4:$C$1000,C4)<>1

It should work OK then. Let me know how you go?