Forum Discussion

HarryE's avatar
HarryE
Copper Contributor
Jun 07, 2019

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 do...
Conditional Formatting
excel
HarryE's avatar
HarryE
Copper Contributor
Jun 07, 2019

SergeiBaklan 

 

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 

Berndvbatanker's avatar
Berndvbatanker
Iron Contributor
Jun 07, 2019

HarryE 

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

http://www.vba-tanker.com

  • HarryE's avatar
    HarryE
    Copper Contributor
    Jun 07, 2019

     Berndvbatanker 

     

    Thank you very much, at an initial look it seems to work. Will confirm soon!

     

    Regards Harry

Resources