excluding condtinally formatted cells from calculation based on color and font with vba

[alosled]

I have created the following function to calculate the sumproduct of two ranges of cells, but only for cell pairs which do not share a cell shade or font color which matches that of another specified cell. It works fine for cells which I have manually formatted the cell shade and font color. However, it seems to fail for cells which have been conditionally formatted. As far as I can tell, it is not even able to recognize that two conditionally formatted cells have a certain shade and font color.

Is there any way to get around this? Or otherwise, is there another way to include/exclude cells from calculation based on their shade and font color?



Function SUMPRODEXCOLOR(rng_one As Range, rng_two As Range, Optional shade_range As Range, Optional text_range As Range)
Dim cSumProd As Double
cSumProd = 0
Dim shadeIndex As Integer
Dim fontIndex As Integer

shadeIndex = shade_range.Interior.ColorIndex
fontIndex = text_range.Font.ColorIndex

For i = 1 To rng_one.Width

If rng_one.Cells(1, i).Interior.ColorIndex <> shadeIndex And rng_one.Cells(1, i).Font.ColorIndex <> fontIndex _
And rng_two.Cells(1, i).Interior.ColorIndex <> shadeIndex And rng_two.Cells(1, i).Font.ColorIndex <> fontIndex Then

cSumProd = cSumProd + rng_one.Cells(1, i) * rng_two.Cells(1, i)

End If


Next

SUMPRODEXCOLOR = cSumProd



End Function

 

[sericom]

I don't think there is an easy way to check the conditional formatting, it's probably easier just to build the same conditional formula into your function.