I have noticed when I apply conditional formatting on larger cell ranges, file size increases rapidly. This post demonstrates how to create dynamic conditional formatting.

What is dynamic conditional formatting?

As new rows or columns are added conditional formatting is instantly applied.

I have tried before to create dynamic conditional formatting but I got weird results. They had a solution where the conditional formatting was dynamically applied using a formula in the Applies to: field.

This post demonstrates how to create dynamic conditional formatting with these steps:

  • Create a dynamic named range
  • "Change Event" to apply conditional formatting to the dynamic named range

I am using the workbook from this post: Quickly highlight records in a list in excel (AND logic) in this dynamic conditional formatting example.

How to create a named range in excel 2007

  1. Click "Formulas" tab on the ribbon.
  2. Click "Name Manager".
  3. Create a new named range: Rng
  4. Type the formula below in "Refers to:" window:
  5. Click close button.

Named range formula:

=Sheet1!$B$7:INDEX(Sheet1!$7:$65536, COUNTA(Sheet1!$B$7:$B$65536), COUNTA(Sheet1!$B$6:$IV$6)+1)

Explaining formula: Create a dynamic named range in excel


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim x As Range
Application.ScreenUpdating = False
Set x = Selection
Range("Rng").PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Where to copy the code?

  1. Right click on sheet name
  2. Click "View Code"
  3. Copy and paste above vba code
  4. Exit visual basic editor

Download excel example file

dynamic conditional formatting.xls
(Excel 97-2003 Workbook *.xls)