Forum Discussion
Conditional Formatting - Color Fill
Hi,
I have set up a conditional format to change the color of a cell based on the value of it. The value is Yes/No and the format is to fill the cells Green/Red. This works fine, however excel doesn't recognize the cells as being filled.
See the picture, where a cell is selected (highlighted in yellow) and the fill is set to none. However, the cell is actually green.
Many Thanks,
Harry
8 Replies
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
