VBA Detecting Color Change

[vahnx]

Using Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) I can execute code when values in the cell change, but it can't check if the foreground or background color changes. What event can detect this? If no event can, is there any way at all?

Edit: Found this, I will look into his loop:
http://www.mrexcel.com/forum/showthread.php?t=574405

 

[mikerickson]

No.
That's one of the reason that treating color as data is not a good idea in Excel.

One could use a SelectionChange event to look at (and remember) the colors of particular cells and note which (if any) have changed. But changing a cell's color would not trigger that, selecting any cell would trigger that. Plus it would slow things down. If the range being checked is large, the slow down would be noticeable and potentially annoying.

If you want to link the contents of a sheet with the color of some cells, going the other way (data entered >> changes in color) is the way that Excel was designed. And Conditional Formatting is good at doing that.

 

[vahnx]

Alright. It's for basically a time sheet. People are coloring their days off red, night shifts dark blue, days blue, etc. Maybe I can do the calculations when someone saves it.

 

[mikerickson]

A Validation drop down with DayOff,NightShift,DayShift etc would be the way I would go.

 

[vahnx]

Modified code from the link I provided and it works!

Just create a Class Module called C_CellColorChange and inside it put:

Private oSh As Worksheet
Private WithEvents cmb As Office.CommandBars

Public Sub ApplyToSheet(Sh As Worksheet)
    Set oSh = Sh
End Sub

Public Sub StartWatching()
    Set cmb = Application.CommandBars
End Sub

Private Sub cmb_OnUpdate()
    Debug.Print "Color of " & ActiveSheet.Name & ":" & Selection.Address & " is " & Range(Selection.Address).Interior.ColorIndex
End Sub

Then inside my ThisWorkbook, and my Workbook_Open() I put

Set oCellColorMonitor = New C_CellColorChange
    oCellColorMonitor.ApplyToSheet ActiveSheet
    oCellColorMonitor.StartWatching

And whenever I click or change the color of a cell it prints the address and color code in the debug window! I'm sure I could trim his code more but not really needed. It doesn't even slow the program or anything.

 

[mikerickson]

What happens when you use that code and change the font size of a cell?