Apply Repeated Conditional Formatting Rules in Excel with VBA
Question details
The user needs a reusable VBA procedure that applies the same conditional formatting styles to many target ranges while using a different reference cell for each range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Apply conditional formatting to 256 cell pairs without duplicating the rule-creation code for every pair.
- Observed behavior
- Each target range should receive the same four rule styles, with formulas that refer to its corresponding reference cell.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and identify the worksheet, target ranges, reference cells, and four formulas you need. If the worksheet is protected, obtain its password and note any custom protection permissions before running code that changes formatting rules.
Create a reusable VBA procedure and call it for each target range
Use one procedure to create the rules and a separate loop to supply each target range and reference cell.
The example below formats B2:C257 as 256 two-cell target ranges, each compared with the reference cell in column A on the same row. It uses four illustrative rules: greater than, less than, equal to, and blank. Replace these formulas and styles with your actual requirements.
The target-cell reference is relative, so each cell in a target range compares its own value with the absolute reference cell. Both ranges must be on the same worksheet.
In Excel, open Developer > Visual Basic, then choose Insert > Module. Paste this procedure into the module: Option Explicit Private Sub ApplyPairRules(ByVal target As Range, ByVal reference As Range) Dim cellAddress As String Dim refAddress As String Dim numericTest As String Dim rule As FormatCondition cellAddress = target.Cells(1, 1).Address(False, False, xlA1) refAddress = reference.Cells(1, 1).Address(True, True, xlA1) numericTest = "AND(ISNUMBER(" & cellAddress & "),ISNUMBER(" & refAddress & ")," target.FormatConditions.Delete Set rule = target.FormatConditions.Add( _ Type:=xlExpression, _ Formula1:="=" & numericTest & cellAddress & ">" & refAddress & ")") rule.Font.Color = RGB(0, 128, 0) Set rule = target.FormatConditions.Add( _ Type:=xlExpression, _ Formula1:="=" & numericTest & cellAddress & "<" & refAddress & ")") rule.Font.Color = RGB(192, 0, 0) Set rule = target.FormatConditions.Add( _ Type:=xlExpression, _ Formula1:="=" & numericTest & cellAddress & "=" & refAddress & ")") rule.Interior.Color = RGB(198, 239, 206) Set rule = target.FormatConditions.Add( _ Type:=xlExpression, _ Formula1:="=" & cellAddress & "=" & Chr(34) & Chr(34)) rule.Interior.Color = RGB(242, 242, 242) End Sub
In the same module, paste the following macro. Replace Sheet1 with your worksheet name and set sheetPassword if needed. Adjust the row loop and worksheet-qualified ranges to match your layout. Public Sub FormatAllPairs() Const sheetPassword As String = "" Dim ws As Worksheet Dim rowNumber As Long Dim previousUpdating As Boolean Dim protectedContents As Boolean Dim protectedObjects As Boolean Dim protectedScenarios As Boolean Dim wasProtected As Boolean Dim wasUnprotected As Boolean Dim failureMessage As String Dim protectionError As String previousUpdating = Application.ScreenUpdating On Error GoTo Failed Set ws = ThisWorkbook.Worksheets("Sheet1") protectedContents = ws.ProtectContents protectedObjects = ws.ProtectDrawingObjects protectedScenarios = ws.ProtectScenarios wasProtected = protectedContents Or protectedObjects Or protectedScenarios If wasProtected Then ws.Unprotect Password:=sheetPassword wasUnprotected = True End If Application.ScreenUpdating = False For rowNumber = 2 To 257 ApplyPairRules ws.Range("B" & rowNumber & ":C" & rowNumber), _ ws.Range("A" & rowNumber) Next rowNumber GoTo CleanUp Failed: failureMessage = Err.Description Resume CleanUp CleanUp: On Error Resume Next If wasUnprotected Then Err.Clear ws.Protect Password:=sheetPassword, _ DrawingObjects:=protectedObjects, _ Contents:=protectedContents, _ Scenarios:=protectedScenarios If Err.Number <> 0 Then protectionError = Err.Description End If Application.ScreenUpdating = previousUpdating On Error GoTo 0 If Len(failureMessage) > 0 Then MsgBox "Formatting stopped: " & failureMessage, vbExclamation End If If Len(protectionError) > 0 Then MsgBox "Worksheet protection could not be restored: " & protectionError, vbExclamation End If End Sub
Return to Excel and choose Developer > Macros > FormatAllPairs > Run. Select B2 and open Home > Conditional Formatting > Manage Rules to verify that its formulas reference $A$2; then check B3 to confirm that its formulas reference $A$3. Test greater, smaller, equal, and blank values before applying the pattern to your full layout.
Use an explicit mapping for target ranges that are not consecutive
Keep the same formatting procedure when the 256 pairs are scattered across the worksheet rather than arranged in consecutive rows.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet editing, with a familiar interface and Excel-format compatibility for an easy transition. It does not fix Excel VBA issues; use Excel for this macro workflow unless your WPS edition explicitly supports the required VBA features.
Frequently Asked Questions
Why should the reference cell be absolute but the target cell be relative?
A formula such as =B2>$A$2 lets Excel adjust B2 for each cell in the target range while keeping the comparison cell fixed at A2. The helper uses Address(False, False) for the target and Address(True, True) for the reference to produce this behavior.
Can I keep existing conditional formatting rules when running the macro?
Yes. Remove target.FormatConditions.Delete from ApplyPairRules if you want to retain existing rules. However, running the macro repeatedly will then add duplicate rules. Open Home > Conditional Formatting > Manage Rules to review rule order, duplicates, and any Stop If True settings.
How do I use my own four formulas and colors?
Open Developer > Visual Basic and edit the four FormatConditions.Add calls in ApplyPairRules. Replace each Formula1 expression and adjust its Font.Color or Interior.Color assignment. Keep the supplied reference address in each formula that needs to compare against the pair's reference cell.
Will worksheet protection and screen updating be restored if the macro fails?
The example routes errors through a cleanup section that restores the previous ScreenUpdating value and attempts to reapply worksheet protection if it was removed. It reports protection failures separately. To retain custom protection permissions, add the corresponding arguments, such as AllowFiltering:=True, to the ws.Protect call.
Can this VBA macro run in Excel for the web or WPS Spreadsheets?
Excel for the web cannot run VBA macros; open the .xlsm file in desktop Excel and use Developer > Macros. WPS VBA availability depends on the edition and environment, so do not assume this Excel macro will run there without verifying support.
