VBA & Macro Problems

Highlight Matching Staff Letters in Excel with VBA

Olivia Miller Oct 10, 2026 869 views

Question details

The user needs a VBA script to compare staff letters in an availability column with activity assignments in other columns, highlighting the cell when a match occurs.

Highlight Matching Staff Letters in Excel Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing availability cells in column Z against activity assignments in columns M through W on the same row, especially when cells contain multiple space-separated letters.
Observed behavior: The user wants to successfully parse space-separated staff letters, trim extra spaces, compare them exactly against target cells, and apply conditional highlighting when a match is found.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and back up your data before executing new VBA scripts.

Solution 1Recommended

Create a VBA Script to Split, Compare, and Highlight

Write a VBA macro that uses the Split function to handle multiple letters, trims white spaces, and loops through the target columns to highlight matches.

When dealing with cells containing multiple space-separated characters, standard conditional formatting may fall short. A VBA loop combined with the Split and Trim functions ensures each individual letter is isolated and accurately compared against your activity columns.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank script window.

Write the Parsing and Comparison Loop

Draft a For loop that iterates through your specific row range in column Z. Use the Split(cell.Value, " ") function to divide the space-separated letters into an array.

Compare and Highlight Matches

Nest another loop to check each letter from the array against columns M through W. Use Trim() to remove extra spaces. If a match is found, apply color using the cell.Interior.Color index.

Run and Verify the Macro

Press F5 or click the Run button. Return to your worksheet to verify that the availability cells with matching staff letters are highlighted.

Create a VBA Script to Split, Compare, and Highlight

Debugging Tips: If matches are not highlighting as expected, use Debug.Print to output the parsed letters into the Immediate Window. Check for exact case matches or use UCase() for case-insensitive comparisons.

Powerful Macro Support

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides outstanding compatibility with Excel macros and VBA scripts. You can easily write, debug, and execute your VBA code to automate complex matching tasks without requiring expensive software.

1. Enable Developer Tools: Open your workbook in WPS Spreadsheet and click the 'Developer' tab on the main ribbon.
2. Launch the Macro Editor: Click on 'Visual Basic' to open the WPS VBA Editor, which offers a familiar interface for Excel users.
3. Paste and Run Your Code: Insert a new module, paste your staff matching script, and click run to instantly highlight matching letters in your spreadsheet.

Fully compatible with Microsoft Excel VBA scripts and .xlsm formats.Built-in Developer tools for macro recording, editing, and debugging.Lightweight installation with incredibly fast script execution speeds.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle multiple staff letters in a single cell using VBA?

You can handle multiple items by using the VBA Split() function. By specifying a space (" ") as the delimiter, Split converts the string into an array, allowing you to iterate through and process each letter individually.

Why isn't my VBA code highlighting the matching letters?

This is usually caused by hidden trailing or leading spaces, or case sensitivity issues. Ensure you wrap your variables in the Trim() function to remove whitespace, and use UCase() on both strings to force a uniform comparison.

Can I run this matching macro automatically when a cell changes?

Yes. Instead of using a standard Module, you can place your macro inside the Worksheet_Change event of your specific sheet. This will trigger the code to run and highlight cells the moment data is entered or modified.

Excel & Data Problems