Forum Discussion
Gregg1960
Sep 30, 2023Copper Contributor
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?
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
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