Conditional Formats "Cell Colors"

[wolf.stalker]

Greetings all.
I recorded a macro that showed me how to highlight a range of cells if a certain condition was met (duplication for example).

Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = vbRed
    .TintAndShade = 0
End With

This works fine but now i am trying to (via VBA) test to see if that cell's color is red, blue, green, etc and if so do something with it but i am having no luck as i am not sure how to test to see if cell has been formatted to a color via a format conditions?

i have tried using

If ActiveCell.FormatConditions = vbRed Then
    ' this returns the value -4142
    ' do something here
end if

but i am starting to realize for some reason, the cell itself is not really the color red? anyone able to help me out?

[xlbo]

Don;t know if there is extra functionality in 2007 for doing this but in xl2003, the only way to test for the cf colour was to mimic the cf condition that produces the colour

In this instance, it looks like you are testing for duplicates so

if your data is in column A then something like

 
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns("A"), activecell.value) > 1 then
    'duplicate
else
    'not a duplicate
End if

hoping someone can show that there is an easier way to do this in 2007 though as this has been a bugbear about excel for a while

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working example

[xlbo]

...but this is being set by conditional formatting and as per your page, the functions don't seem to cover that unless I'm missing somethng

[Bob Phillips]

You are right, I gave the wrong link. It should have been http://xldynamic.com/source/xld.CFConditions.html