Conditional Formatting Issue

Welcome to the forum!

What is the CF formula that you are currently using?

Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

I've done a fair bit of Googling on this issue, and I think your only hope is a VBA solution, so I'm moving the thread to that section.

You are causing your own problem by inserting the row
- don't do that and the problem goes away

Alternative approach:
- copy existing values (or formulas) down 1 row and clear values in top row
- position of conditional formatting cells unaffected
- conditional formatting formula remains unchanged

Sub CopyDown()

    Dim lr As Long, rng As Range
    Sheets("GAHC").Activate
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("B11:L" & lr)
    rng.Copy
    rng.Offset(1, 0).PasteSpecial (xlPasteValues)
    Range("B11:I11").ClearContents
    
End Sub

or if you want to retain formulas use:

    rng.Offset(1, 0).PasteSpecial (xlPasteFormulas)

Another alternative method
- pull in your values from sheet3 by formula using INDIRECT

In sheet "GAHC" cell B11, then copy down and across to columns B:i (columns J,K L have formula)

Formula:

=INDIRECT("Sheet3!" &  ADDRESS(ROW()-10,COLUMN()-1))

Copy down to row 200?? to make room for more data

When new row1 inserted in sheet3
- all the values in "GAHC" move down automatically
- rows are not inserted in "GAHC- so conditional formatting keeps working


To test
- see attached workbook
- insert new row1 in sheet3 and see what happens in other sheet
(WARNING:I have not re-worked the formulas in columns J K L - some are formula, others are values )