Sum cells by color in a sub routine

[John J]

As part of a sub routine I am trying to sum by color. But I am getting "'Runtime Error '13': Type Miss match" on the line "For Each cl In sRange"

If any one can put me in the right direction then Thanks in advance.

The full code is below.

Sub Reconcile()

Dim ActWs As Worksheet
Dim ColA As Long ' ColA = Column A
Dim R As Long 'R = Row

Set ActWs = ThisWorkbook.ActiveSheet 'ActWs = active Worksheet.

'ActWsLastRow = The number of rows to the last entry in Column A
ActWsLastRow = ActWs.Range("A" & Rows.Count).End(xlUp).Row


ActWs.Range("A" & ActWsLastRow).Offset(4, 1).Value = "opening balance"
ActWs.Range("A" & ActWsLastRow).Offset(4, 2).Value = Range("C8")
ActWs.Range("A" & ActWsLastRow).Offset(5, 1).Value = "Add Sales"
ActWs.Range("A" & ActWsLastRow).Offset(5, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 2)
ActWs.Range("A" & ActWsLastRow).Offset(6, 1).Value = "Total OB + Sales"
ActWs.Range("A" & ActWsLastRow).Offset(6, 2).Value = Range("A" & ActWsLastRow).Offset(4, 2) + _
Range("A" & ActWsLastRow).Offset(5, 2)
ActWs.Range("A" & ActWsLastRow).Offset(7, 1).Value = "Deduct purcheses and Transfers"
ActWs.Range("A" & ActWsLastRow).Offset(7, 2).Value = Range("A" & ActWsLastRow).Offset(-3, 3)
ActWs.Range("A" & ActWsLastRow).Offset(8, 1).Value = "Balance in Zero"
ActWs.Range("A" & ActWsLastRow).Offset(8, 2).Value = Range("A" & ActWsLastRow).Offset(6, 2) - _
Range("A" & ActWsLastRow).Offset(7, 2)
ActWs.Range("A" & ActWsLastRow).Offset(9, 1).Value = "Deduct Credits to be paid next month"




' Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
Dim sRange1 As Range


Set sRange1 = Range(Range("A" & ActWsLastRow).Offset(-54, 3), Range("A" & ActWsLastRow).Offset(-4, 3)) _
' Or use "Set sRange1 = Range("D" & ActWsLastRow - 54).Resize(51)"

ColIndex = 43

For Each cl In sRange 'Runtime Error '13': Type Miss match
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
' SumByColor = cSum

ActWs.Range("A" & ActWsLastRow).Offset(9, 2).Value = cSum
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.