PDA

View Full Version : Conditional Formats "Cell Colors"



wolf.stalker
09-23-2010, 03:00 PM
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).SetFirstPriori ty
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
09-23-2010, 06:19 PM
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
09-24-2010, 02:28 AM
See http://www.xldynamic.com/source/xld.ColourCounter.html for a working example

xlbo
09-26-2010, 04:28 PM
...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
09-26-2010, 11:59 PM
You are right, I gave the wrong link. It should have been http://xldynamic.com/source/xld.CFConditions.html