sumbycolor has stopped working

[EddieL]

Hi All

I had this procedure working perfectly but it suddenly stopped and is not recognising CL as a variable. I have highlighted the CL in blue which is what excel does when it shows "Expected Function or Variable". Could anyone help please? I have remmed out col1 = CL.Interior.ColorIndex as I could not see it having any value in this code.

Function SumByColor(CellColor As Range, rRange As Range)
'Stop
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each CL In rRange
'col1 = CL.Interior.ColorIndex
If CL.Interior.ColorIndex = ColIndex Then
If CL = "AL7.5" Then ct = 7.5
If CL = "AL12.5" Then ct = 12.5
If CL = "ST" Then ct = 7.5
If CL = "SK7.5" Then ct = 7.5
If CL = "SK12.5" Then ct = 12.5
If CL = "CL7.5" Then ct = 7.5
If CL = "CL12.5" Then ct = 12.5
If CL = "CL13.5" Then ct = 13.5
If CL = "M7.5" Then ct = 7.5
If CL = "M12.5" Then ct = 12.5
If CL = "M13.5" Then ct = 13.5
cSum = WorksheetFunction.Sum(ct, cSum)
End If
Next CL
SumByColor = cSum
End Function

 

[Fluff]

Hi & welcome to MrExcel.
Add this to your declarations

Dim Cl as Range

 

[EddieL]

Hi Fluff

Thank you so much. I thought I had tried that, but after adding it I now have it working perfectly again.

Thanks once again.

EddieL

 

[Fluff]

You're welcome & thanks for the feedback