Forum Discussion

Gregg1960's avatar
Gregg1960
Copper Contributor
Sep 30, 2023

Building a formula referencing Conditional Formatting colors

I'm trying to sum a column based on Conditional Formatting colors. In my worksheet, we use conditional formatting to change a cell's color to one of five shades of green based on its value. My partner wants to assign a simple numerical value to each shade of green, then sum up those values in each column. For example, a column with three cells in very light green (1 point each, total of 3) and one in very dark green (5) would return a sum of 8.

 

We found a VBA function that returns the Index value of each color, which we can then use to sum up the column using IF statements: https://techcommunity.microsoft.com/t5/excel/formula-or-function-for-if-statement-based-on-cell-color/m-p/78321/highlight/true#M9352

Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End Function

 

This works perfectly for the background color of the cell, but NOT for the conditionally formatted background of the cell. Is there a way to adjust this module to return the conditionally formatted background of the cell? I thought using DisplayFormat.Interior.ColorIndex might work, but it doesn't seem to.

 

Can anyone help?

  • Gregg1960 

    The Range object has a property DisplayFormat the represents the formatting as actually displayed on the screen, taking both direct formatting and conditional formatting into account.

    Unfortunately, DisplayFormat cannot be used directly in a VBA function in a cell formula - it causes an error. But it's possible by using a trick - see Using DisplayFormat in a UDF 

  • Gregg1960 

    The Range object has a property DisplayFormat the represents the formatting as actually displayed on the screen, taking both direct formatting and conditional formatting into account.

    Unfortunately, DisplayFormat cannot be used directly in a VBA function in a cell formula - it causes an error. But it's possible by using a trick - see Using DisplayFormat in a UDF 

      • Niketan's avatar
        Niketan
        Copper Contributor

        Hi Gregg1960 ,

        Can you please share excel sheet with this applied VBA example?

        That'll be so helpful. Thanks in advance!!

         

        Actually I'm trying to sum of conditionally formatted color (green & yellow) cells values.

         

Resources