0

I'm trying to color format cells in an Excel 2013 worksheet. I'm particularly interested in highlighting cells with names values that occur more than 2 times (i.e. triplicate names) in the same column. But, I have to highlight only the third repeated cell and ones after it. I have tried many VBA scripts I found elsewhere, but as I not an Excel expert, I was not succesful until now. Anyone could help on this?

Please check this screenshot here (https://imgur.com/a/CmXoD) of the spreadsheet which shows the formula I'm trying now after some suggestions.

Thank you in advance for helping.

1
  • conditional formatting generally doesn't require VBA, why not just looking to Excel conditional formatting functionality? Mar 27, 2018 at 13:38

1 Answer 1

1

You can do it without macro:

  • Select your data you want to format
  • go to home - conditional formatting - new rule
  • select "use a formula to determine which cell to format"
  • enter formula =COUNTIF($A$1:A2,A2)>2, of course you need to update cell references:
    • $A$1 is the cell above your range
    • A2 is the first (and active!!) cell of range to be formatted
  • click "format" and select highlight format

enter image description here

4
  • Keep in mind that this will color every repetition after the third one, just in case you don't want this.
    – arana
    Mar 27, 2018 at 14:55
  • 1
    That's fairly obvious from the picture, isn't it? If OP wants only the third occurrence highlighted, he can replace ">2" in the formula with "=3". Mar 27, 2018 at 16:05
  • First of all, Thank you guys for helping. But, the suggested formula '=COUNTIF($A$1:A2,A2)>2' is not working. Excel shows me an error message (something is missing or wrong with the formula). To avoid the error message I have used a semicolon instead of a colon in the function and now the error message is gone. The case here is that it still not highlighting the names (Please, check screenshot here: imgur.com/a/zGEti). Will the COUNTIF function work for names or only numbers? Mar 27, 2018 at 19:13
  • yes, depending on your regional settings you may need to use ; instead of ,. Please post a screenshot where formula is visible too. Please don't post it in a comment, but update your question. Yes, COUNTIF works with text too. Mar 27, 2018 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.