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

Joined
Jul 31, 2015
Messages
231
Reaction score
33
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
 
Joined
Jul 31, 2015
Messages
231
Reaction score
33
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.
 
Joined
Feb 22, 2014
Messages
1,641
Reaction score
341
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.


 
Joined
Feb 22, 2014
Messages
1,641
Reaction score
341
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.
 
Joined
Jul 31, 2015
Messages
231
Reaction score
33
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
 
Joined
Feb 22, 2014
Messages
1,641
Reaction score
341
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.
 
Joined
Jul 31, 2015
Messages
231
Reaction score
33
Thank you. Any thoughts you have will increase my understanding.

regards

Alan
 
Joined
Feb 22, 2014
Messages
1,641
Reaction score
341
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

Excel Conditional Formatting VBA Code.png
 
Joined
Jul 31, 2015
Messages
231
Reaction score
33
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top