Hello All
Created a spreadsheet which uses various coloured backgrounds to highlight cells depending on status of projects. Each coloured cell contains a figure.
When I change the background colour the sum does not automatically update.
Please can you review and advise.
Many thanks
In the cell I have =sumbycolor(A143,F129:F200)
The macro:
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
ps - the spreadsheet is set to automatically update.
Created a spreadsheet which uses various coloured backgrounds to highlight cells depending on status of projects. Each coloured cell contains a figure.
When I change the background colour the sum does not automatically update.
Please can you review and advise.
Many thanks
In the cell I have =sumbycolor(A143,F129:F200)
The macro:
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
ps - the spreadsheet is set to automatically update.