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

enter image description here

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).

enter image description here

  • Have you tried using full column references for the conditional formatting?
    – user385793
    Feb 4, 2020 at 3:37
  • @Jeeped What do you mean by full column reference? Do you mean referencing the entire column (A:A)? If so, this doesn't work because I have more than one conditional formatting rule and can't apply a single rule to the entire column
    – Alex
    Feb 4, 2020 at 7:53
  • 1
    As long as you're not supplying specifics then I'll agree with you; it can't be done. If you provided specifics then I'd bet it could be done.
    – user385793
    Feb 4, 2020 at 7:55
  • @Jeeped Edited it to add images, hope that makes it clearer
    – Alex
    Feb 4, 2020 at 8:12

4 Answers 4


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)* 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) & ","
        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.

  • I think the problem might be that I don't have any headers. This way Excel can't determine if the new row belongs to the table or should be a row above the table
    – Alex
    Feb 3, 2020 at 15:24
  • This may be useful Feb 3, 2020 at 15:35
  • @ZygD The tables (or alternatively, named ranges) are not separate, it's one big table with one header up top and tons of date below.
    – Alex
    Feb 4, 2020 at 7:51
  • Thank you! I will go through this code tomorrow or on the weekend, just one quick question. What does If Target.Address = Target.EntireRow.Address exactly test? I've used Worksheet_Change before, but back then I also had problems understanding the If condition.
    – Alex
    Feb 6, 2020 at 17:46
  • I'm happy it worked for you. It tests whether the range that you changed is an entire row (or several entire rows). This normally happens when you include or delete one or more rows. So the code runs further only if you included rows or deleted. Later the code checks for deleting vs inserting and only proceeds when it was an insertion procedure.
    – ZygD
    Feb 8, 2020 at 9:50

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.

  • The problem with copying formatting over is that it creates a new rule for every new row. Managing the rules easily becomes such a nightmare that the only viable option is to remove all rules applied to the table and redo them. And that isn't really viable.
    – Noein
    Jul 29, 2020 at 7:36
  • @Noein In that case, the initial portion of my answer should still stand (since it's simply extending the range bounds; please correct me if I'm wrong). I simply mentioned the latter as a quicker option when it becomes necessary to do so.
    – Arctiic
    Jul 29, 2020 at 22:49

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


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 :).

  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 16 at 16:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .