One way:
Function TestColour(r As Range, rColour As Range)
Dim vColour As Variant
Dim j As Long, k As Long
Application.Volatile
' only countiguous areas
If r.Areas.Count <> 1 Then Exit Function
If r.Count = 1 Then
TestColour = (r.Interior.Color = rColour.Interior.Color)
Else
ReDim vColour(1 To r.Rows.Count, 1 To r.Columns.Count)
For j = 1 To r.Rows.Count
For k = 1 To r.Columns.Count
vColour(j, k) = (r(j, k).Interior.Color = rColour.Interior.Color)
Next k
Next j
TestColour = vColour
End If
End Function
=SUMPRODUCT(('OL BC'!$B$10:$B$22=SUMALL!E11)*testColour('OL BC'!$K$10:$K$22,SUMALL!$F$10))
using F10 as the source of the reference color.
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]
If it's too slow, delete "Application.volatile" and remember to recalculate (f9) after any shadings are changed.
Bookmarks