Building a formula referencing Conditional Formatting colors

[Gregg1960]

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-colo...

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?

[#2 · Accepted solution]

@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 

[#3]

Yes, that works! Thank you very much.