detect a color change in a cell

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
' You can call this function from a worksheet cell with a formula like:
' =SUMBYCOLOR(A1:A10,3,FALSE)

Dim rng As Range
Dim OK As Boolean

Application.Volatile True
For Each rng In InRange.Cells
    If OfText = True Then
        OK = (rng.Font.ColorIndex = WhatColorIndex)
    Else
        OK = (rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(rng.Value) Then
        SumByColor = SumByColor + rng.Value
    End If
Next rng

End Function

Select allOpen in new window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim priorColor As Long
Dim currentColor As Long
Dim myPriorCell As Range


Application.EnableEvents = False
On Error GoTo endcheck 'nothing in the stack, so nothing to process

    'find last cell selected and color before change
    Application.Undo 'now we're back at the last active cell, and the before color is present
    Set myPriorCell = ActiveCell
    priorColor = myPriorCell.Interior.Color
    
    'revert back and get color from last selected cell after change, if any
    Application.Undo 'now the active cell is back where it was, and the color of the prior active cell is what it was most recently changed to
    currentColor = myPriorCell.Interior.Color
    
    'clear undo stack
    Range("A1").Copy Range("A1")

    'now get cursor back to where it was headed
    Target.Select
    
    If currentColor <> priorColor Then
        'PUT OFFSET CODE LOGIC HERE - priorCell is the range of the changed color, priorColor it its old color, currentColor is its new color
        MsgBox "colors were changed, need to set offset logic", vbOKOnly
    End If

endcheck:
Application.EnableEvents = True

End Sub

Select allOpen in new window