I need to find the colour of a cell in Excel - but when it's been applied with conditional formatting

[Alan Sh]

Someone gave me a great routine to count the colour of cells in a range.

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

But - that doesn't work if the colour is applied with conditional formatting. And I can't find a routine which does work for that. I thought it would be "DisplayFormat.Interior" but that doesn't work (for me).

Does anyone have any idea how I can get the colour count if the colour is applied with conditional formatting?

Thanks for any advice

Alan

 

[ubuysa]

Does anyone have any idea how I can get the colour count if the colour is applied with conditional formatting?

Can you not test the conditional expression itself?

 

[Alan Sh]

That is sort of what I have done.

I have solved the issue for now by adding a new column with the same test and then counting that to put the result somewhere. I then hide the column so it's not visible.

 

[clifford_cooley]

My guess would be to learn how to pull formatconditions in VBA. Its late here and my mind is tired. It is an interesting idea. I might look into it further tomorrow.

With MyRange.FormatConditions(1).Interior
'Set the cellbackground color
.Color = 13551615
End With

That will set the color. But if you can set the color you can pull the color.

Using Conditional Formatting with Excel VBA

Excel Conditional Formatting Excel Conditional Formatting allows you to define rules which determine cell formatting. For example, you can create a rule

www.automateexcel.com

 

[Alan Sh]

Interesting ideas. I'll have a play.

 

[clifford_cooley]

OK! I finally took an hour or two to look at this. I was wrong in thinking it was "formatconditions". The key was looking at "displayformat". Which you stated you have already tried.

"Selection.DisplayFormat.Interior.Color" will pull the color of the selected Format Conditioned cell.

This line works for me. Cell A1 will match the color of any selected Display Formatted cell.
Cells(1, 1).Interior.Color = Selection.DisplayFormat.Interior.Color

Looking at the code you are trying to implement. I'm not certain how you are trying to use it. It is not clear exactly what is being compared in the loop.

 

[Alan Sh]

What I am trying to do is to look at a column's worth of colours which have been set bu a conditional format and count the number that match a colour I have set below.

So, I have the routine as:

Function CountDcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.DisplayFormat.Interior.Color = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

and I call it like this:

=countDcolor(E9:E79,$B$82)

Where the first parameter is the column and the second one is a cell where I show the colour I am comparing against.

What I get back is "#VALUE!"

So, something isn't right, but I don't know what. IfiI change "Interior.Color" to "Interior.ColorIndex" I get the same error. So, it looks like there is no "Color" or "ColorIndex" to "DisplayFormat.Interior". But you say it worked? Any thoughts?

Alan

 

[clifford_cooley]

But you say it worked? Any thoughts?

Alan

I'm able to pull the color from the page. I'll now need to look at the code to see why it is not working for you. Thanks for the clarity. I now understand what it is you are doing and how you are calling the code.

Please be patient though. I'm only a hobbiest that loves to tinker with VBA, and a handful of other programming languages.

 

[Alan Sh]

Thank you. Any thoughts you have will increase my understanding.

regards

Alan

 

[clifford_cooley]

I've run into a wall. Looks to be the same wall you ran into. The code returns the correct number but only displays "#value!" for some reason. Seems to be an internal issue with Excel not displaying the results that were returned.

Here is the code I eventually ran with.

Function CountDcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long

'I ran into a few forum post about configuring the
'Function and variables as "Double" instead of "Long"
'I tried that but the returned result didn't change

xcolor = criteria.Interior.Color
'This could also be xcolor = criteria.Displayformat.Interior.Color
CountDcolor = 0
For Each datax In range_data
If datax.DisplayFormat.Interior.Color = xcolor Then
CountDcolor = CountDcolor + 1 'This variable has to be named the same as the function
'This variable is what is returned by calling the function
End If
Next datax

End Function

Here is an image of my worksheet

 

[Alan Sh]

I'm glad it's not just me. As I said above, I've found a workaround, but only by creating (and hiding) an extra column where I basically duplicated the conditional formatting equation and then counting the results in that column.

Not pretty, but it works.

Thanks for having a look.

regards

Alan