Auto-expand conditional formatting range to include new columns/rows

[jake020]

I like to apply conditional formatting when tracking metrics to quickly identify things such as top and bottom 3 months of performance, however, whenever I add a new month (typically as a new column) the formatting does not include the new data, so I need to manually change the Applies To range for each item. This can take a long time since there are a lot of metrics to track and this needs to be done each month. Using the Format Painter also doesn't seem to solve the issue since that will usually apply the same Rule to the new column but will only apply to that column. So for instance, I will have a rule highlight Top 1 that Applies To $C$3:$H$3 and if I add data to column J and use Format Painter, I will still have a rule highlighting Top 1 for cells $C$3:$H$3 but will then also have a rule highlighting Top 1 for cell J3.

Is there any way to make it so that the conditional formatting rule will automatically expand the Applies To range to include data entered in a new column?

 

[Worf]

Welcome to the Board

Are you familiar with VBA?

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i%, fc
For i = 1 To Target.Parent.UsedRange.FormatConditions.count
    Set fc = Target.Parent.UsedRange.FormatConditions(i)
    If Not Intersect(fc.AppliesTo, Target.EntireRow) Is Nothing And Len(Target) _
    Then fc.ModifyAppliesToRange Union(fc.AppliesTo, Target)
Next
End Sub

 

[jake020]

Unfortunately I'm not too experienced with VBA, is there any other way to achieve this?

 

[Worf]

For automated behavior, no. Try the workbook below, which implements the code I posted before.

Adding data to the right will expand the conditional formatting.

expand.xlsm

Shared with Dropbox

www.dropbox.com

 

[xp15]

use range 1:1000, it may change to full range (A1:AZ1000, or something like that), but it will update as you add more lines or columns, so it will stay full range