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.

If vCurColor(i) = xlNone Then Exit Sub
in various places in the class module, but no avail. Can you help out?
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