macro to check if any cells in a column have turned red due to conditional formating

[tonywatsonhelp]

Hi Everyone,

I have a big problem with duplicates and I'm trying to get it sorted out.
however I'd like a warning message box to tell me when one has been identified.

currently I use conditional formatting to turn the cell red if its a duplicate.
but I don't go to the tab very often and it hold a lot of data so I was hoping I could get a macro that would warn me if duplicates are there.

So here's what I would like.

A macro that when I run it looks at sheet "Capacity" column "AM" and check to see if any cells have been conditionally turn red (I say conditionally as this is different to filling it red manual)
if it finds any red cells a message box pops up saying "Possible Duplicates in capacity sheet!"

please help if you can

Thanks

Tony

 

[James006]

Hi,

If I am not mistaken ... Chip Pearson has designed a solution ... a long time ago ...


http://www.cpearson.com/Excel/CFColors.htm


Hope this will help

 

[RoryA]

If you have 2010 or later:

dim rg as range, cell as range
with Sheets("Capacity")
   set rg = .range("AM2", .cells(.rows.count, "AM").end(xlup))
end with
for each cell in rg.cells
   if cell.displayformat.interior.color = vbred and cell.interior.color <> vbred then
      msgbox "there are duplicates
      exit for
   end if
next cell

 

[tonywatsonhelp]

Works perfectly,

thank you RoryA

Tony