Counting by colours

[lewislusby]

Hi,
I've used a macro to sum cells of a similar colour and then used "=SumColor(cell,range)" and that works well BUT now I want to sum the cells in the column next to where the coloured cells are. Do I need to include a vlookup to the formula? Stuck!

 

[Ralajer]

Welcome to the board lewislusby,

The following is a modified version of a SumColor Function that takes a third argument which represents the column offset to sum. Called like this
'=SumColor(cell,range,offset)' If the offset 0 it will work just like SumColor. When the offset is a negative or positive number it will sum the cells that number left or right. If the offset is not zero the range may only be one column wide.

Function SumColorOffset( _
            matchCell As Range, _
            checkRng As Range, _
            colOffset As Integer)

    Dim cell As Range
    Dim matchColor As Integer
    Dim tempSum

    '// Offset Bounds Checking
    If checkRng.Column + colOffset < 1 Or _
            checkRng.Column + colOffset > Columns.Count Or _
            (checkRng.Columns.Count <> 1 And colOffset <> 0) Then
        SumColorOffset = CVErr(xlValue)
        GoTo errorExit
    End If

    matchColor = matchCell.Interior.ColorIndex
    
    For Each cell In checkRng
        If cell.Interior.ColorIndex = matchColor Then
            tempSum = WorksheetFunction.Sum(cell.Offset(0, colOffset)) + tempSum
        End If
    Next cell
    
    SumColorOffset = tempSum
errorExit:

End Function