Solved How to change the color font on repeated cell in Excel?

January 19, 2014 at 10:31:06
Specs: Windows 7
Hi Admin,

Please help me solve my issue in excel.

Scenario 1:
I want to change color of the font to white automatically in excel wherever there is repetition or duplicate value within a column range. But I want the first word to be in black. The reason to make the duplicate one to be white because I dont want to show it visible but if you click on the cell, the value is still there.

Scenario 2:
Same issue but there are 2 or more cells repetition. Let say we have 5 same value text or number in five adjacent cells, I want 4 of them to be white, the topmost to retain as black or any color as long it is not white.

Scenario 3:
All data within that column are generated thru vlookup from another tab.This time I have 2-5 column range to consider.

Question1, Can I just do it using formula to input in formula bar?or it can be done only using vba?By te way im using 200t excel. If so, can you guide me to make equation for this please.

Thanking you in advance.



See More: How to change the color font on repeated cell in Excel?

Report •

January 20, 2014 at 17:46:47
✔ Best Answer
This can be done using the Conditional Formatting feature.

Follow the steps found at the site I provided the link for below.

First read the section on how to highlight duplicate cells and how to apply the format to the entire column using Copy/PasteSpecial. You should choose a white font color instead of a fill pattern.

Then read the section on the correct formula to use when you don't want the first occurrence to be "highlighted", or in your case, not to be formatted with a white font.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

January 22, 2014 at 09:58:50
I got it with the formula you mentioned in the first, i found myself confused following the instruction because im using excel 2007 and the version in your tutorial is 2003. There's a little bit difference between the two. But because you explain it very well, I manage to solve the issue. Thank you very much Mr. Derbydad for your goodness.

More power to you and I wish you a good health.


message edited by AldrinB

Report •

Related Solutions

Ask Question