VBA & Macro Problems

How to Highlight Numbers Below 500 Using Excel VBA Macro

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants a VBA macro to apply conditional formatting to a selected range, specifically highlighting cells containing numeric values strictly less than 500 while making sure blank cells remain completely unformatted.

Product: Excel
Device & OS: not provided
Scenario: Automating spreadsheet formatting to visually flag low values without writing complex iterative loop codes that falsely trigger on empty cells.
Observed behavior: When applying conditional rules through VBA, values under 500 should be highlighted with specific font and background colors, while empty cells must be ignored.

Before you start

Before running the macro, ensure you have enabled Developer tools in Excel and saved your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code upon closing.

Solution 1Recommended

Apply Conditional Formatting via VBA

Use a simple VBA subroutine to clear existing conditional formatting on a selection and add a new rule targeting values under 500.

This method leverages Excel's native FormatConditions collection. By setting the operator to xlLess and the value to 500, the macro effectively targets the correct numeric range. Because it uses conditional formatting rather than a static background color loop, it automatically handles empty cells without falsely highlighting them as zero.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

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

Paste the Macro Code

Copy and paste the following code into the module: Sub HighlightUnder500() With Selection.FormatConditions .Delete With .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=500") .Font.Bold = True .Interior.Color = 49407 End With End With End Sub

Run the Macro

Return to your worksheet, select the range of cells you want to format, and press Alt + F8. Select 'HighlightUnder500' and click 'Run'.

Color Customization: The property .Interior.Color = 49407 applies a specific orange shade. You can change this number or use standard color constants like vbRed or vbYellow to customize the highlight color.

Efficient Spreadsheet Management

Use WPS Spreadsheet for Advanced Formatting and Macros

WPS Office offers robust support for spreadsheet automation and conditional formatting. You can easily manage large datasets, apply complex visual rules, and run macros just like you would in Microsoft Excel.

1. Select Data: Highlight the cells you want to format in WPS Spreadsheet.
2. Open Conditional Formatting: Go to the Home tab and click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Less Than'.
3. Set the Rule: Type '500' into the dialog box and choose your preferred highlight style. Click OK to apply instantly without writing any macro code.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) formats.Advanced built-in conditional formatting rules without needing code.Supports VBA macros for complex workflow automation.Lightweight software that processes large data files quickly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA code highlight empty cells as zero?

If you use a basic 'If Cell.Value < 500 Then' loop in VBA, Excel evaluates empty cells as 0, which triggers the highlight. Using the .FormatConditions method natively avoids this because Excel's built-in conditional formatting ignores completely blank cells by default.

How can I change the VBA macro to highlight numbers above a specific value instead?

In the .Add method of the VBA script, change Operator:=xlLess to Operator:=xlGreater and adjust Formula1:="=500" to the threshold number you wish to use.

Will this macro overwrite my existing cell formatting?

The line .Delete inside the 'With Selection.FormatConditions' block removes any pre-existing conditional formatting rules on the selected cells before applying the new rule. However, it will not remove static background colors applied manually.

Can I run this macro across the entire worksheet?

Yes, but it is highly recommended to select the specific data range first. Running it across all millions of cells in an entire worksheet might significantly slow down your workbook due to the massive number of applied conditional formatting rules.

Excel & Data Problems