VBA & Macro Problems

How to Apply Conditional Formatting Through the Last Used Row in Excel VBA

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user needs a VBA script to dynamically apply conditional formatting only up to the last populated row in a dataset, rather than applying the format to an entire column including blank cells.

How to Apply Conditional Formatting Through the Last Used Row in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating conditional formatting rules for datasets of variable lengths.
Observed behavior: Formatting is successfully applied exclusively to the active data range, keeping the workbook lightweight and visually clean.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) so your VBA script runs properly, and back up your data before executing new macros.

Solution 1Recommended

Use VBA to Determine the Last Row and Apply Formatting Rules

Dynamically calculate the last populated row in a specific column using VBA, then apply conditional formatting exclusively to that exact range.

Applying conditional formatting to an entire column (e.g., C:C) applies the rule to over a million cells, which severely degrades spreadsheet performance. By using VBA to find the last used row, you ensure that formats are only calculated where actual data exists.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Visual Basic for Applications Editor, then click 'Insert' > 'Module' to create a new script area.

Declare the LastRow Variable

Type 'Dim LastRow As Long' at the start of your subroutine to create a variable that will store the dynamic row number.

Find the Last Used Row

Use the End(xlUp) method by entering 'LastRow = Range("C" & Rows.Count).End(xlUp).Row'. Change the letter 'C' if your target data is in a different column.

Clear Existing Formats in the Range

Target your specific range using 'With Range("C2:C" & LastRow).FormatConditions' and add '.Delete' on the next line to clear out overlapping rules before adding new ones.

Add the New Conditional Formatting Rule

Add the condition by typing 'With .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=37")', then set the color with '.Interior.Color = vbRed', and close both 'With' blocks.

Use VBA to Determine the Last Row and Apply Formatting Rules

Adjusting the VBA Script: Make sure to change the column reference 'C' to match your actual dataset. You can also change the Operator (e.g., xlEqual, xlLess) and Formula1 value to match your specific conditional logic.

Advanced Macros in WPS Spreadsheet

Automate Conditional Formatting with WPS Office

WPS Spreadsheet fully supports VBA macros and advanced conditional formatting. You can run dynamic scripts to easily format large datasets without compromising spreadsheet performance.

1. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your dataset or existing .xlsm file.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the macro environment.
3. Insert the Dynamic Script: Paste your LastRow conditional formatting code into a Module.
4. Run the Macro: Click 'Run Sub' or trigger the macro via a custom button to instantly format your active data range.

Seamless compatibility with Microsoft Excel (.xlsx, .xlsm, .xls) macro filesBuilt-in VBA editor for writing and editing automation scriptsFast processing engine that handles complex conditional rules smoothlyFree to download with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why should I avoid applying conditional formatting to an entire column?

Applying a rule to an entire column forces Excel to calculate formatting for 1,048,576 rows. This drastically increases your file size and slows down saving, calculating, and scrolling speeds.

How do I change the highlight color in my VBA script?

You can modify the '.Interior.Color' property in the script. You can use standard visual basic constants like vbRed, vbGreen, vbBlue, or use the RGB function like RGB(255, 255, 0) for a custom yellow.

Can I format multiple columns based on the last row using this code?

Yes. You can expand the target range within the code. For example, instead of 'Range("C2:C" & LastRow)', you can use 'Range("A2:F" & LastRow)'. Just ensure your conditional formatting formula uses the correct absolute referencing (e.g., '=$C2>37') so the entire row highlights properly.

Excel & Data Problems