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"
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"