VBA & Macro Problems

How to Color the Intersection of Two Values in Excel Using VBA

John Wilson Sep 30, 2026 868 views

Question details

The user wants to use a VBA macro to find the row and column matching specific criteria (like a location and a quarter) and highlight the intersecting cell without permanently saving the lookup values.

How to Color the Intersection of Two Values in Excel Using VBA

Product: Excel
Device & OS: not provided
Scenario: Creating an interactive checklist where entering new response variables and clicking a button automatically updates the background color of the corresponding cell in a master table.
Observed behavior: Looking for a programmatic method to dynamically target and format specific data intersections as responses arrive, as standard pivot tables or static formulas do not fit the checklist requirement.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet software to access the VBA editor, and verify that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to preserve your code.

Solution 1Recommended

Use a VBA Macro to Find and Color the Intersecting Cell

This solution uses the Match function within VBA to locate the specific row and column headers, then applies a fill color to the cell where they intersect.

By utilizing the WorksheetFunction.Match method in VBA, you can quickly find the relative positions of your lookup values (e.g., location and quarter). Once the row and column indices are identified, the macro targets the exact intersecting cell and modifies its Interior.Color property.

This method is highly dynamic and can be tied to a clickable button, allowing users to update the checklist instantly without storing temporary lookup data.

Open the VBA Editor

Navigate to the Developer tab on your ribbon and click 'Visual Basic', or press ALT + F11 on your keyboard to open the VBA Editor.

Insert a New Module

In the Project Explorer pane on the left, right-click on your workbook name, select 'Insert', and then click 'Module' to create a blank canvas for your script.

Write the VBA Code

Create a Sub procedure that declares your search values. Use 'Application.WorksheetFunction.Match' to find the row number of the location and the column number of the quarter. Set the cell color using 'Cells(rowNum, colNum).Interior.Color = vbYellow'.

Assign the Macro to a Button

Return to your worksheet, go to the Developer tab, click 'Insert', and choose a Button under Form Controls. Draw it on the sheet and select your new macro from the 'Assign Macro' dialog box so it runs on a single click.

Use a VBA Macro to Find and Color the Intersecting Cell

Handle Errors Gracefully: Add 'On Error Resume Next' before your Match functions to prevent the macro from crashing if the location or quarter is misspelled or missing from the table.

Solution 2

Use Worksheet_Change Event for Automatic Highlighting

Instead of requiring a button click, this method automatically colors the intersection cell the moment the user types or selects the new location and quarter values.

Easily Create and Run VBA Macros in WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to automate complex checklist tasks and highlight data intersections effortlessly. The intuitive developer interface ensures your Excel VBA scripts run smoothly without requiring any modifications.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the checklist data.
2. Access Developer Tools: Click on the 'Developer' tab in the top ribbon and select 'VBA Editor'.
3. Write or Paste Your Code: Insert a new module and paste your intersection-coloring script perfectly into the editor.
4. Run the Automation: Assign the macro to a button or click 'Run' to instantly highlight the targeted intersection cell.

Excellent compatibility with Microsoft Excel VBA syntax and .xlsm formats.Built-in Developer tools and robust VBA Editor included.Lightweight program that executes complex automation scripts rapidly.Free to use for everyday office tasks and checklist management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I highlight intersecting cells without using VBA?

Yes, you can use Conditional Formatting. Select your data table, go to Conditional Formatting > New Rule, and use a formula like =AND($A2=$Lookup_Location, B$1=$Lookup_Quarter) to highlight the cell automatically.

Why is my VBA code returning a 'Subscript out of range' error?

This error typically occurs if the worksheet name referenced in your VBA code does not perfectly match the actual tab name in your workbook. Check for typos or extra spaces in your sheet names.

How do I clear the previous cell colors before highlighting a new intersection?

Before applying the new color in your macro, you can clear the entire table's background color by adding a line like Range("B2:F10").Interior.Pattern = xlNone at the beginning of your script.

Where can I get more help with advanced VBA programming?

For highly specific VBA issues, communities like Stack Overflow are excellent resources. Be sure to tag your question with 'vba' and 'excel' and provide a snippet of your current code for the best results.

Excel & Data Problems