change language:

Quite a lot of standard events are available in Excel, such as Workbook_Open and Worksheet_SelectionChange, but changing the background color of a cell does not trigger an event. This can sometimes be desirable (for example in the case of Override conditional formatting). However, it is possible to define a separate event for this.This article demonstrates how that works.

Create class module clCellColorChange

The new CellColorChange event will be handled by a class module. To do this, a new class module must be created using the VBA editor (via Insert->Class Module). The name of this module must then be changed to clCellColorChange. The following code must be placed in this class module:

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, all rights reserved
' Date      : 2020-11-09
' Version   : 1.0
' Purpose   : Create CellColorChange event
'--------------------------------------------------------------------------------------------------------------------------

Private WithEvents CmdBar As Office.CommandBars
Private oWks As Worksheet, bAllCellsViewed As Boolean
Private vCurColor() As Variant, vPrevColor() As Variant, sSelectionAddress As String

Public Sub SetActiveWorksheet(wks As Worksheet)
    Set oWks = wks
    Set CmdBar = Application.CommandBars
End Sub

Private Sub CmdBar_OnUpdate()
    Dim rngCurSelection As Range, i As Long, rngCell As Range
    Set rngCurSelection = Selection
    If sSelectionAddress <> rngCurSelection.Address Then
        Erase vCurColor
        Erase vPrevColor
        sSelectionAddress = ""
        bAllCellsViewed = False
    End If
    On Error Resume Next
    For Each rngCell In rngCurSelection
        ReDim Preserve vCurColor(i)
        vCurColor(i) = rngCell.Interior.Color
        If bAllCellsViewed Then
            If vPrevColor(i) <> vCurColor(i) Then
                vPrevColor(i) = vCurColor(i)
                CallByName oWks, "CellColorChange", VbMethod, rngCell
            End If
        End If
        i = i + 1
        If i >= rngCurSelection.Cells.Count Then
            bAllCellsViewed = True
            ReDim Preserve vPrevColor(UBound(vCurColor))
            vPrevColor = vCurColor
        End If
    Next
    On Error GoTo 0
    sSelectionAddress = rngCurSelection.Address
End Sub

Private Sub Class_Terminate()
    Set CmdBar = Nothing
End Sub

This class module is the heart of the event. It uses the OnUpdate event of the Commandbars. Each time this event is fired, it checks whether a cell color has changed in the selected cells. If so, the CellColorChange event of the applicable worksheet is called.

Other code

Just creating this class module is of course not enough. For this to work, this class module must be bound to the appropriate worksheets. In this example I assume that this event should be able to be used in all worksheets of the file concerned. This requires the following code which must be placed in ThisWorkbook:

Private oCellColorEvent As clCellColorChange

Private Sub Workbook_Open()
    Set oCellColorEvent = New clCellColorChange
    oCellColorEvent.SetActiveWorksheet ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set oCellColorEvent = New clCellColorChange
    oCellColorEvent.SetActiveWorksheet ActiveSheet
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set oCellColorEvent = Nothing
End Sub

Private Sub Workbook_Deactivate()
    Set oCellColorEvent = Nothing
End Sub

So every time when a sheet is selected, the class is bound to this sheet. To be able to use the event in the relevant sheet, the following code must be placed behind each sheet:

Public Sub CellColorChange(TargetRange As Range)
    'own code
    MsgBox "Cell color of " & ActiveSheet.Name & "!" & TargetRange.Address & " changed!"
End Sub

The code that must be executed at the CellColorChange event can then be placed in the place of own code.

Finally

A few more comments:

  • This code takes advantage of the OnUpdate event of the CommandBars. This event is fired very often in practice and therefore the CellColorChange event is evaluated very often. This can slow down large files.
  • The event is generated for each changed cell in the selection. So if multiple cells have changed, the event will also fire multiple times.
  • With this code all cells of a selection are evaluated.This can cause problems with very large selections. If necessary, this can be taken care of in the class by first counting the number of cells in the selection and, if the number is too large, add the command Exit Sub.
  • The event does not respond to cell color changes made by the fill handle copy. However, this copy action triggers the Worksheet_Change event and possibly the event could be handled there, but that will not be considered here.
  • The principle of this CellColorChange event can be used to create other custom events.
Download an example file:
zip-2Create CellColorChange event 1.0
 
Comments  
# Kevin 2023-12-29 22:05
This code works great, thank you. I am wondering if there is a way to trap the color change event, if the color is changed back to No Fill? We change the cell to yellow so someone else can do their work, then they change it back to No Fill. I have tried to insert the line
If vCurColor(i) = xlNone Then Exit Sub
in various places in the class module, but no avail. Can you help out?
Reply
# Manfred 2023-12-30 13:47
Hi Kevin,
You can achieve this by addding the following if-statement to the class module:
If vCurColor(i) = &HFFFFFF Then
CallByName oWks, "CellColorChange", VbMethod, rngCell
End If
Reply
# Kevin 2024-01-26 22:51
Thank you so much for the help. This works flawlessly for what I am trying to accomplish.
Reply
arrow_up