Forum Discussion
Conditional Formatting - Color Fill
HarryE ,
Conditional formatting overwrites cell color format. It doesn't matter what do you have in Fill. The latest will be applied only to the cells for which conditional formatting doesn't trigger format changing.
Thanks for your quick reply, the end goal of what i'm trying to do is set up a live count of which cells are red and which are green.
So I have my conditional formatting set up to color the cells based on the value and then i can use a "CountCellsByColor" formula to count the no. of red vs green. However when conditional formatting is applied this formula doesn't work because it reads what color the cells is filled with. Which with conditional formatting it is classed as no fill.
I was hoping there was a way to make conditional formatting actually fill the cell and change it as normal based on the value, so that the formula works and I could have a live feed of what the count is.
Thanks for your help, any pointers appreciated!
Harry
Hi Harry,
there is a way with vba to do this: see attachment, too.
Sub ReadColor()
Dim rngcell As Range
For Each rngcell In Selection
Debug.Print "cell-color: " & rngcell.Interior.ColorIndex
Debug.Print "cell-color cond. format: " & rngcell.DisplayFormat.Interior.ColorIndex
Next rngcell
End SubBest regards from germany
Bernd
http://www.vba-tanker.com
Thank you very much, at an initial look it seems to work. Will confirm soon!
Regards Harry
HarryE ,
Here are at least couple of threads how to do calculations based on conditional formatting color (VBA).
At the same time why don't you COUNTIFS() all Yes and No?
HarryEIs there a reason you want to do this indirectly via the colour? Why not use a COUNTIF function and directly count how many of the cells are True and how many are False, or Yes vs No, or whatever the underlying data is?
