VBA & Macro Problems

How to Highlight Cells When Selecting Another Cell in Excel Using VBA

Natalie Taylor Sep 28, 2026 868 views

Question details

The user wants to automatically highlight a specific target range in an Excel worksheet whenever another specific cell is clicked or selected.

How to Highlight Cells When Selecting Another Cell in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating cell formatting to draw attention to specific data or instructions when a user navigates to a designated trigger cell.
Observed behavior: A VBA script uses the Worksheet_SelectionChange event to dynamically clear previous formats and apply a background color to a target range upon selecting the monitored cell.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) to retain the VBA code, and verify that macros are allowed in your application's Trust Center settings.

Solution 1Recommended

Use the Worksheet_SelectionChange Event in VBA

Create a VBA macro that listens for cell selection changes. It will clear existing highlights and apply a new fill color to your target range when the specified cell is clicked.

This method uses an event handler built into the worksheet. Whenever the selection changes, the code checks if the newly selected cell is your trigger cell (e.g., B1). If it is, it highlights the target range (e.g., A1:A10).

Open the VBA Editor

Open your Excel workbook and press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Access the Worksheet Code Window

In the Project Explorer panel on the left, locate your workbook and double-click the specific worksheet (e.g., Sheet1) where you want the highlight effect to occur.

Paste the VBA Code

Copy and paste the following code into the blank window on the right: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range Set rng = Range("A1:A10") rng.Interior.ColorIndex = xlNone If Not Intersect(Target, Range("B1")) Is Nothing Then rng.Interior.Color = RGB(255, 255, 0) End If End Sub

Test the Macro

Close the VBA editor and return to your Excel worksheet. Click on cell B1. The range A1:A10 should immediately highlight in yellow. You can modify 'A1:A10' and 'B1' in the code to suit your actual data layout.

Use the Worksheet_SelectionChange Event in VBA

Customizing the Highlight Color: The color is determined by the RGB(255, 255, 0) value, which produces yellow. You can change these numbers (from 0 to 255) to create different highlight colors.

Automate Cell Highlighting Easily with WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, enabling you to run automated scripts like cell highlighting seamlessly. Enjoy a high-performance, lightweight office suite that handles your advanced data tasks effortlessly.

1. Open Workbook in WPS: Launch WPS Spreadsheet and open your document.
2. Launch the VBA Editor: Navigate to the 'Developer' tab and click 'VBA Editor', or simply press Alt + F11.
3. Apply the Macro: Double-click your current worksheet in the side panel, paste the Worksheet_SelectionChange code, and save the file.

Fully compatible with Microsoft Excel formats (.xlsm) and VBA codeLightweight application with fast startup and execution speedsFamiliar user interface requiring zero learning curveComprehensive built-in data formatting and analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the highlight color in the VBA code?

You can change the highlight color by modifying the RGB values in the line `rng.Interior.Color = RGB(255, 255, 0)`. For example, `RGB(255, 0, 0)` will make it red, and `RGB(0, 255, 0)` will make it green.

Why doesn't the highlight disappear when I select a different cell?

If the highlight persists, ensure your code includes the line `rng.Interior.ColorIndex = xlNone` before the `If` statement. This command clears the existing background color every time a new cell is selected, before evaluating if the trigger cell was clicked.

Can I highlight multiple different target ranges using this method?

Yes. You can expand the VBA code by adding `ElseIf` statements or separate `If Not Intersect(...)` blocks for different trigger cells, assigning a unique target range and color to each condition within the same Worksheet_SelectionChange event.

Will this macro work if I share the file with others?

Yes, provided you save the file as a Macro-Enabled Workbook (.xlsm) and the recipients enable macros when they open the file in their spreadsheet application.

Excel & Data Problems