Jun 07 2019 01:52 AM - edited Jun 07 2019 02:21 AM
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
Jun 07 2019 02:49 AM
@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.
Jun 07 2019 02:59 AM
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
Jun 07 2019 03:34 AM
@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?
Jun 07 2019 03:39 AM - edited Jun 07 2019 03:40 AM
Hi Adam,
The underlying data varies, the content is different.
So the color is a way of presenting if the data/answer is correct/incorrect - Green/Red
But I will look into it to see if there is a way round using this methods, thanks!
Regards
Harry
Jun 07 2019 03:42 AM
@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?
Jun 07 2019 03:48 AM
Jun 07 2019 03:58 AM
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 Sub
Best regards from germany
Bernd
Jun 07 2019 04:05 AM
Thank you very much, at an initial look it seems to work. Will confirm soon!
Regards Harry