How to show value in cell depending on its filled color - event in Excel VBA

0
votes

In Excel I would like to show value in current cell depending on its filled color ( something like IFCOLOR() ). Excel should do this automatically when I change filled color therefore it should be event.

For example: When I fill cell in green then Excel automatically shows value 100 When I fill cell in red then automatically Excel shows value 75 and so on ...

Is it possible do this by event in Excel VBA? Or can you give me other ideas how to do it?

I used Workbook_SheetChange but this works when I change value in cell not its background color.

Regards Jan

2
excelvbaevents
Since there is no event in Excel which handles color changes for a cell you'll have to either (1) use another event which you hope to occur often and fast after the color change (for example SelectionChange event) or (2) you implement your own event handler. The latter of the two would work the following way: when the Excel file is opened you start a sub which is scheduled to run every x seconds with Application.OnTime. This sub scans the sheet&range you wish to monitor for any changes and then applies the designated color code – Ralph
@JanZitniak have you tried my code? – BOB

2 Answers

1
votes

You can try something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If ActiveCell.Interior.Color = vbRed Then
       ActiveCell = 75
   Else
       ActiveCell = " "
   End If
End Sub

With a predefind range:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim rng As Range, cell As Range
Set rng = ws.Range(Cells(1, 1), Cells(100, 20))

For Each cell In rng
    If cell.Interior.Color = RGB(255, 0, 0) Then
        cell = 75
        ElseIf cell.Interior.Color = RGB(0, 255, 0) Then
            cell = 100
    Else
        cell = " "
    End If

Next cell

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
0
votes

CommandBars.OnUpdate can be used to handle most custom events. In the ThisWorkbook object:

Private WithEvents bars As CommandBars, color As Double
Private Sub bars_OnUpdate()
    'If Not ActiveSheet Is Sheet1 Then Exit Sub ' optional to ignore other sheets
    If ActiveCell.Interior.color = color Then Exit Sub ' optional to ignore if same color
    color = Selection.Interior.color
    'Debug.Print Selection.Address(0, 0), Hex(color)
    If color = vbGreen Then Selection = 100 Else _
    If color = vbRed Then Selection = 75
End Sub

Private Sub Workbook_Activate()
    Set bars = Application.CommandBars ' to set the bars_OnUpdate event hook
End Sub
Private Sub Workbook_Deactivate()
    Set bars = Nothing ' optional to unset the bars_OnUpdate event hook
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    color = Selection.Interior.color ' optional to ignore selection change events
End Sub

The above sample doesn't handle all edge cases, but can be adjust as needed.

For other custom events, the more specific CommandBarControl events should be used if possible:

CommandBarButton.Click
CommandBarComboBox.Change
CommandBarControl.OnAction
CommandBarPopup.OnAction