Conditional Formatting coloring does not change the cell color value?

[Marcouss]

Hi All,

I've been using the below macro to count the number of Red cells vs Green Cells.

The macro is working fine and have shared it for reference, my issue is that in order to optimize my excel, I am trying to use Conditional formatting to color my cells (eg: Red/Green) rather than doing it manually.

However with Conditional formatting my Macro does not capture that new cell color.

For ex:
** If I have 2 Red cells and 2 Green cells, macro returns 2 for red-count and 2 for green-count.
I apply my conditional formatting in a way to turn all my cells Red, but macro still returns 2 red and 2 Green.
I change the color of all cells manually to red and now macro counts 4 red.****


This is the macro but I believe its more of a conditional formatting issue.
Macro calling in Excel is : CountCcolor(C2:C19, I2)
C2:C19 is my range to count cell colors, and I2 is the my choice of color (I2 cell is colored Red/Green).
I tried coloring I2 cell with conditional formatting and get the same problem it needs to be colored manually for macro to work.

Seems like the conditional formatting doesn't really change the color value of a Cell? Excel has been lying to me from the beginning .. I will never trust it again

------MACRO-----
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
----End of Macro---

I'm open to your suggestions

Thank you in advance for your help and let me know if I have to change anything in my "Configuration"

 

[Steve_R]

http://www.mrexcel.com/forum/excel-questions/916272-running-macro-conditional-formatting.html

 

[skywriter]

If you use DisplayFormat.Interior.ColorIndex it will give you the color of the cell regardless of whether it's caused by conditional formatting or is just the assigned color of the cell. The problem with it is that it doesn't work in custom functions.
So there are a couple of options, you could run the macro manually, the code could be assigned to the worksheet calculate or change events etc.

NOTE: I2 is the same color green as what you see in C2 and C19, but for some reason is showing up darker in this post and C6 is not white it's blue like the other cells. Excel Jeanie does some weird things.

So here in cells C2 to C19 I have conditional formatting that turns the cell green if the number in the cell is greater than 10 and as you can see the result in I3 is a count of 2. I'm using I2 which is simply colored green as the example color to count. So if you are interested in somehow using this as a solution, let me know.

Sub CountConditionalFormatting()
Dim rngColoredCells As Range, r As Range
Dim rngExampleCell As Range, c As Long
Set rngColoredCells = Range("C2:C19")
Set rngExampleCell = Range("I2")
For Each r In rngColoredCells
If r.DisplayFormat.Interior.ColorIndex = rngExampleCell.Interior.ColorIndex Then
    c = c + 1
End If
Next r
Range("I3").Value = c
End Sub

Sheet1

*	C	D	E	F	G	H	I
2	15	*	*	*	*	*	*
3	2	*	*	*	*	*	2
4	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*
19	15	*	*	*	*	*	*

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format C2 1. / Formula is =C2>10 Abc C3 1. / Formula is =C2>10 Abc C4 1. / Formula is =C2>10 Abc C5 1. / Formula is =C2>10 Abc C6 1. / Formula is =C2>10 Abc C7 1. / Formula is =C2>10 Abc C8 1. / Formula is =C2>10 Abc C9 1. / Formula is =C2>10 Abc C10 1. / Formula is =C2>10 Abc C11 1. / Formula is =C2>10 Abc C12 1. / Formula is =C2>10 Abc C13 1. / Formula is =C2>10 Abc C14 1. / Formula is =C2>10 Abc C15 1. / Formula is =C2>10 Abc C16 1. / Formula is =C2>10 Abc C17 1. / Formula is =C2>10 Abc C18 1. / Formula is =C2>10 Abc C19 1. / Formula is =C2>10 Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Excel 2010
	C	D	E	F	G	H	I
2	15
3	2						2
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19	15
Sheet1

 

[Marcouss]

Hi Skywriter,

Thanks for your response, I've updated my excel to test your recommended solution and doesn't seem to work or I implemented it wrong.

When I do a step by step, the IF statement is comparing 16 vs 50 for same color cells (I copy pasted the cell C2 into I2 to be sure and they are still different
C2 "DisplayFormat.Interior.ColorIndex" is 16 and I2 "DisplayFormat.Interior.ColorIndex" is 50

What am I doing wrong here?

 

[Marcouss]

Skywriter,

Correction it worked! my conditional Formatting was still blocking the macro.. had to delete everything.

So this is a temporary Fix... because I am working with 10 different Color Formatting with a new sheet per week and 10 different columns in my Range Selection.
And the count result goes at the bottom of every column.

Previously this was easy given I colored the cells manually, once I was done with the column I had the count automatically at the bottom which helped my keep track as I worked (I need to see the progress of the count while updating the column).

No I'll need to run the macro every other cell.

I will keep looking for an automated solution using Conditional Formatting as I appreciate the fact that the cell color instantly once done.

Let me know if you have any other ideas

Thanks for all

 

[skywriter]

My pleasure, thanks for the feedback.