Conditional Formatting range changes after deleting rows

youdaman 

Any idea why…

---

It's designed that way, of course.  When you move or copy cells, their formats and conditional formats go with them.  When you insert empty cells inside a range, they get the formats and conditional formats from cells above them.  And similarly for cell deletions.

… and how I can avoid this?

---

Apparently you can't.  But you likely can restore the range for your conditional format after rows are deleted.

If you want the range restored only when your macro deletes rows, include the following code in that macro, after the deletion code:

    Dim objCondFormats  As FormatConditions
    Dim objCondFormat   As Object
    '
    Set objCondFormats = Range("C125").FormatConditions
    Set objCondFormat = objCondFormats.Item(1)
    objCondFormat.ModifyAppliesToRange Range("C125:X221")

(Depending on what your existing code does, you may need to qualify those two Range objects with the appropriate Worksheet object.)

There are a couple of potential problems:

• If the entire range of rows is deleted at once, you may also lose the specific conditional format.  I will just mention that VBA code can create (add) a conditional format.  See the documentation hyperlink below.
• If you have more than one conditional format that applies to the upper-left cell (C125), the item index for the CondFormats object might need to be an integer larger than 1.  You must correctly identify which conditional format is to be modified.

If you want the range restored even if the user deletes/inserts rows, then in the Worksheet_Change event, include the following code:

If Not (Intersect(Target, Range("C125:X221")) Is Nothing) Then
    Dim objCondFormats  As FormatConditions
    Dim objCondFormat   As Object
    '
    Set objCondFormats = Range("C125").FormatConditions
    Set objCondFormat = objCondFormats.Item(1)
    objCondFormat.ModifyAppliesToRange Range("C125:X221")
End If

That code will restore the range even for changes that are not deletions, a minor waste of processing.  The same considerations as listed above apply.

The Microsoft documentation on the FormatConditions object (a collection) seems to say that it contains only FormatCondition (singular) objects.  But the collection may contain FormatCondition and/or AboveAverage, ColorScale, Top10, and UniqueValues objects (and possibly others).  That's why I defined objCondFormat "As Object" rather than "As FormatCondition".  Each of those classes (objects from those classes) support the ModifyAppliesToRange method.  For very-slightly improved performance, you can specify the specific class name for your conditional format.

If that does not immediately resolve your issue, I hope it puts you on the right track to a solution.