VBA & Macro Problems

How to Create an Excel VBA Exact-Match Conditional Formatting Formula

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs an Excel VBA conditional formatting formula that specifically highlights text based on an exact case-sensitive match.

How to Create an Excel VBA Exact-Match Conditional Formatting Formula

Product: Excel
Device & OS: not provided
Scenario: Writing an Excel VBA macro to apply conditional formatting rules that differentiate between text variations (e.g., highlighting 'Service' but ignoring 'SERVICE').
Observed behavior: Standard conditional formatting or VBA xlContains methods highlight all variations of the text regardless of case, failing to restrict formatting to the exact specified text.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your Excel ribbon to allow VBA script execution.

Solution 1Recommended

Use the EXACT Formula in VBA Conditional Formatting

Apply a conditional formatting rule using the exact-match formula within VBA to enforce case-sensitivity, bypassing the default case-insensitive xlContains method.

To achieve a true case-sensitive text match in Excel conditional formatting through VBA, you must use a formula based on the EXACT function. This ensures only the precisely typed string triggers the formatting.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Excel VBA Editor, then click Insert > Module to create a new script area.

Define the target range

In your new macro, declare the range of cells you wish to apply the formatting to, for example: Set rng = ActiveSheet.Range("A1:A100")

Clear existing formats

Add rng.FormatConditions.Delete to ensure previous conflicting conditional formatting rules are removed before applying the new exact-match rule.

Add the EXACT formula condition

Use the Add method to inject the formula: rng.FormatConditions.Add Type:=xlExpression, Formula1:="=EXACT(A1,""Service"")"

Set the highlight style

Define the appearance of the matched cells, such as setting the background color: rng.FormatConditions(1).Interior.Color = vbYellow

Use the EXACT Formula in VBA Conditional Formatting

Handling VBA Errors: If the VBA code returns an error, try testing the macro in a reduced, simplified copy of your workbook. This helps isolate whether the problem is due to specific workbook data, conflicting macros, or syntax errors.

Solution 2

Apply Case-Sensitive Formatting Manually via Ribbon

If you are encountering persistent macro errors, you can achieve the exact same case-sensitive formatting result manually using the standard Conditional Formatting menu.

Use WPS Spreadsheets for VBA

Run Exact-Match Formatting Macros Seamlessly in WPS Office

WPS Spreadsheets provides comprehensive support for complex conditional formatting and is highly compatible with Microsoft Excel VBA scripts, allowing you to easily execute your exact-match macros.

1. Open your macro workbook: Launch WPS Spreadsheets and open your .xlsm file containing the exact-match data.
2. Access the Developer tab: Navigate to the Developer tab on the ribbon to launch the built-in VBA Editor.
3. Paste and execute your script: Insert your formatting script utilizing the EXACT formula and click Run to highlight your specific text case.
4. Manage formatting rules: Use the Home > Conditional Formatting > Manage Rules menu to review and tweak the rule applied by your macro.

Fully compatible with Microsoft Excel (.xlsx, .xlsm) formats and conditional formatting rules.Integrated Developer tools allow you to write, edit, and run VBA macros natively.Lightweight architecture ensures fast script execution even on large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA conditional formatting highlight everything regardless of text case?

Standard text matching in conditional formatting, as well as the 'xlContains' parameter in VBA, is case-insensitive by default. It will treat 'Service' and 'SERVICE' as the same string. To differentiate them, you must use a formula condition utilizing the EXACT function.

What should I do if my exact-match VBA macro returns a runtime error?

First, capture the exact error code and message. To troubleshoot, create a reduced copy of the workbook without sensitive information and run the macro there. This helps isolate whether the error is caused by conflicting workbook data, protected ranges, or syntax issues.

Can I highlight multiple exact words using the EXACT function in a single VBA rule?

Yes. You can combine the EXACT function with the OR function within your VBA conditional formatting formula. For example, setting your formula string to "=OR(EXACT(A1,""Service""), EXACT(A1,""Product""))" will highlight cells containing either exact word.

Excel & Data Problems