How to highlight the color of an active cell in Excel

To highlight the color of an active cell in Excel, you need to use a combination of VBA (Visual Basic for Applications) to dynamically change the cell color when it’s selected. Here’s how you can do it:

Step 1: Open the VBA Editor

  1. Open your Excel workbook.
  2. Press Alt + F11 to open the VBA editor.

Step 2: Insert a New Module

  1. In the VBA editor, find your workbook in the “Project Explorer” window.
  2. Right-click on the workbook name and select Insert > Module.

Step 3: Add VBA Code

Copy and paste the following code into the new module:

Dim PrevCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    ' Restore the previous cell's color
    If Not PrevCell Is Nothing Then
        PrevCell.Interior.ColorIndex = xlNone
    End If
    ' Highlight the active cell
    Target.Interior.Color = RGB(255, 255, 0) ' Yellow color
    ' Set the active cell as the previous cell for next change
    Set PrevCell = Target
    On Error GoTo 0
End Sub

Step 4: Save and Close the VBA Editor

  1. Close the VBA editor by clicking the X button or pressing Alt + Q.
  2. Save your workbook as a macro-enabled workbook (.xlsm).

Step 5: Test the Code

  1. Go back to your Excel worksheet.
  2. Click on different cells. The active cell should now be highlighted in yellow.

Explanation of the Code

  • Dim PrevCell As Range: This declares a variable to keep track of the previously selected cell.
  • Workbook_SheetSelectionChange: This event is triggered every time a new cell is selected.
  • On Error Resume Next: This line is used to ignore any errors that might occur.
  • If Not PrevCell Is Nothing Then PrevCell.Interior.ColorIndex = xlNone: This restores the original color of the previously selected cell.
  • Target.Interior.Color = RGB(255, 255, 0): This sets the color of the active cell to yellow.
  • Set PrevCell = Target: This updates the previous cell reference to the current cell.

Now, whenever you select a cell in your workbook, it will be highlighted in yellow, and the previous cell’s highlight will be removed.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.