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
- Click "Formulas" tab on the ribbon.
- Click "Name Manager".
- Create a new named range: Rng
- Type the formula below in "Refers to:" window:
- Click close button.
Named range formula:
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").Rows(1).Copy Range("Rng").PasteSpecial Paste:=xlFormats Application.CutCopyMode = False x.Select Application.ScreenUpdating = True Application.EnableEvents = True End Sub
Where to copy the code?
- Right click on sheet name
- Click "View Code"
- Copy and paste above vba code
- Exit visual basic editor
Download excel example file
dynamic conditional formatting.xls
(Excel 97-2003 Workbook *.xls)