[Excel VBA] Change Cell Color to Yellow with “F9” and Red with “F10”

Background

Previously, I created a tool where “pressing the F9 key runs code within a specific cell range.” This time, I tried to expand functionality to support both the F9 and F10 keys.

Specifications

• Target Range: Cells S1:S3.
• F9 Key: When the cursor is in the target range, pressing F9 changes the cell color to Yellow.
• F10 Key: When the cursor is in the target range, pressing F10 changes the cell color to Red.

The Code

Here is the VBA code. It is divided into the Sheet Module (to detect selection changes) and a Standard Module (to handle the actual coloring logic).

1. Sheet Module (e.g., Sheet1)

This code detects when you move the cursor and assigns or resets the keys accordingly.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next ' Ignore errors and continue

    ' Reset F9 and F10 keys to their normal Excel behavior
    ' This ensures they work normally if you click outside the target range
    Application.OnKey "{F9}"
    Application.OnKey "{F10}"

    ' Check if cursor is in the target range and set F9 key action
    Call SetF9Key(Target)

    ' Check if cursor is in the target range and set F10 key action
    Call SetF10Key(Target)

    On Error GoTo 0 ' Reset error handling
End Sub

Private Sub Worksheet_Deactivate()
    ' When leaving this sheet, ensure keys return to normal behavior
    On Error Resume Next
    Application.OnKey "{F9}"
    Application.OnKey "{F10}"
    On Error GoTo 0
End Sub

2. Standard Module (Module1)

This code contains the logic to assign the keys and the actual color-changing macros.

Sub SetF9Key(ByVal Target As Range)
    ' If the selection intersects with S1:S3, assign the macro
    If Not Intersect(Target, ActiveSheet.Range("S1:S3")) Is Nothing Then
        Application.OnKey "{F9}", "ChangeColorToYellow"
        Debug.Print "F9 key has been set."
    End If
End Sub

Sub SetF10Key(ByVal Target As Range)
    ' If the selection intersects with S1:S3, assign the macro
    If Not Intersect(Target, ActiveSheet.Range("S1:S3")) Is Nothing Then
        Application.OnKey "{F10}", "ChangeColorToRed"
        Debug.Print "F10 key has been set."
    End If
End Sub

Sub ChangeColorToYellow()
    On Error Resume Next
    ' Ensure only one cell is selected to avoid accidental mass coloring
    If Selection.Cells.Count = 1 Then
        ' Double-check that we are still in the valid range
        If Not Intersect(Selection, ActiveSheet.Range("S1:S3")) Is Nothing Then
            Selection.Interior.Color = RGB(255, 255, 0) ' Yellow
            Debug.Print "Cell color changed to Yellow."
        End If
    End If
    On Error GoTo 0
End Sub

Sub ChangeColorToRed()
    On Error Resume Next
    If Selection.Cells.Count = 1 Then
        If Not Intersect(Selection, ActiveSheet.Range("S1:S3")) Is Nothing Then
            Selection.Interior.Color = RGB(255, 0, 0) ' Red
            Debug.Print "Cell color changed to Red."
        End If
    End If
    On Error GoTo 0
End Sub

How to Verify

You can check if the code is working correctly by looking at the Immediate Window in the VBE (Visual Basic Editor).

1. Open the VBE (Alt + F11) and ensure the Immediate Window is visible (View > Immediate Window or Ctrl+G).
2. Select cell S1, S2, or S3.
   • You should see: F9 key has been set. / F10 key has been set.
3. Press F9.
   • The cell should turn yellow.
   • Log: Cell color changed to Yellow.
4. Press F10.
   • The cell should turn red.
   • Log: Cell color changed to Red.

Summary

By using Application.OnKey within the SelectionChange event, you can create dynamic keyboard shortcuts that only trigger in specific areas of your spreadsheet.