Excel: Automatically extending conditional formatting to a new row added above data

Question

I have a bunch of values across multiple rows and columns. They are structured so that each row shows the data for one particular month and the most recent values are up top. Consequently, when I update it with new data, I insert a new row above the existing data.

How can I make sure that the conditional formatting rules I have applied to the old range get applied to the new range (with one more row)? I tried using tables or named ranges, but since I add the row above the old data, Excel doesn't automatically extend the named range/the table.

edit: That's the existing data

And I want to add January (marked in red) to this. If every subcategory had an empty row above it (like row 9), I could work around it, but unfortunately that's not the case (see row 5).

Answer 1

Having in mind your data structure, I think you can only reach what you want using VBA. I created something similar to your data structure and wrote a script which runs every time rows are added.

1. Save your file as Excel Macro-Enabled Workbook (.xlsm)*
2. Open VBA Editor by Ctrl+F11
3. In the VBA Editor, double click on your sheet in the left-side pane.
4. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iTargRowsCnt As Long
  Dim oFCond As FormatCondition
  Dim sFCondRanges() As String
  Dim i As Long
  Dim blnFCondRangeModified As Boolean
  Dim sNewAppliesTo As String

  If Target.Address = Target.EntireRow.Address Then
    iTargRowsCnt = Target.Rows.Count
    For Each oFCond In Me.UsedRange.FormatConditions
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      For i = LBound(sFCondRanges) To UBound(sFCondRanges)
        If Not Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) Is Nothing Then
          sFCondRanges(i) = Range(Range(sFCondRanges(i)), Range(sFCondRanges(i)).Offset(-iTargRowsCnt)).Address
          blnFCondRangeModified = True
        End If
      Next 'i
      If blnFCondRangeModified Then
        For i = LBound(sFCondRanges) To UBound(sFCondRanges)
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        Next
        sNewAppliesTo = Left(sNewAppliesTo, Len(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange Range(sNewAppliesTo)
        blnFCondRangeModified = False
        sNewAppliesTo = ""
      End If
    Next 'oFCond
  End If

End Sub

This code will run automatically every time you insert rows. But be cautious while using VBA. Save more often, something unexpected may occur, although I tried to mitigate risks. E.g., it is difficult to design a code to work well with Undo functionality. In this case, Conditional formats would get distorted if you inserted rows and then used Undo.

Answer 2

I would just stick with ranges. Include an extra row at the top when applying the conditional formatting, then hide it or do what you will with it visually (I would notate somewhere it's purpose as a buffer row personally, so it doesn't get removed by mistake).

Another method would be to copy the topmost row then paste-as-formatting over your new row (not at a computer at the moment, need to verify if this works for conditional formatting or not). EDIT: confirmed working for conditional formatting in O365 Business Plus: Excel 2016.

Answer 3

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

Answer 4

After struggling with a similar challenge I found that simply referring to the table name in the "Apply to:" entry automatically adjusts to any change in the table including adding lines :).