ColorFunction with VBA Not Working

[EvanDef]

Hello Excel Gurus,

I am performing interval analysis with conditional formatting that highlight cells in a row where either an interval was missed or a defect is evident in reporting. Interval misses are red and defects are yellow. I am using the following VBA below in conjunction with the below ColorFunction formula in two separate columns at the end of each row. One column to calculate the number of interval misses and next to it one to calculate the number of defects. The problem i am having is once I plug in the ColorFunction formula below at the end of the row, nothing calculates. It leaves the formula just sitting there. The workbook is saved as .XLSM and the code appears to be applied to Sheet1 and Module1. I am still learning the nuances of VBA so I may have overlooked something simple to apply the macro. Any suggestions?

Formula: {=ColorFunction($AV$1,A2:AQ2,FALSE)} and {=ColorFunction($AW$1,A2:AQ2,FALSE)}
Where AV1=Interval Miss and references the CF color red, AW1=Defect Count and references the CF color yellow.

VBA:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Thanks!

 

[Snakehips]

EvanDef,

The problem you have is that .Interior.ColorIndex returns the base colour set for the cell not the colour that it might be displaying as a result of conditional formatting.

Sadly, whilst not impossible, getting the current CF colour is not the easiest of things to do.

See this link..... Conditional Formatting Colors

or Rick Rothstein @.... Get Displayed Cell Color (whether from Conditional Formatting or not)

for more info on how.

You may want to consider using some other criteria other than the colour to make your count. Eg on similar basis to your CF formula?

Hope that helps.

 

[EvanDef]

Thank you my friend. I will give those a shot. Do you know if this is a limitation to just Excel 2010 and prior or does this also apply to Excel 2013. An easy way to count conditional format colors would be a huge benefit to the work that I do. Hopefully the Microsoft Overlords are listening!

Take Care, Evan