Conditional Formatting - Changing to #REF!

Hi All

I have a master sheet, which has some conditional formatting in L18 down. The formula is this: =L18>$D$9+7

This is set and working fine.

In the process, we load in some data from another workbook, and put it into the master sheet with this code:

'##### Clears out the unwanted data on import sheet & copies across ####
With ThisWorkbook.Sheets("Import")
    .Rows("1:17").Delete
    
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("L1:N" & Lastrow).Copy ThisWorkbook.Sheets("Master").Range("L18")
End With
'#######################################################################

As soon as this part of the code runs however, it somehow messes up the conditional formatting on L18 down in a weird way...

Turning it from the correct formula: =L18>$D$9+7
To: =L18>#REF!+7

I don't understand why/how its losing the "$D$9"?

There is another conditional formatting formula that is slightly different but it does the same thing, it loses the "$D$9" replacing it with the not working "#REF!"

Any idea what is going on, and how do I fix it?

Re: Conditional Formatting - Changing to #REF!

It deletes rows 1 to 17 on the import sheet, but row 9 still very much exists on the master sheet which is where the conditional formatting is.

Unless your saying that when it copies across the data from "import" sheet to "master" sheet it copies the conditional formatting also?

Re: Conditional Formatting - Changing to #REF!

Basically here is the sequence of events.

Master sheet has a bunch of data on it, including conditional formatting (as above).

> This master sheet is saved as a xlsx (to remove code) the person A sents it to person B, Person B enter in data into column L. Column L then highlights etc due to the conditional formatting rules.
>> Person B save this (with the added data) and send back to person A
>>> Person A runs the code to import the data that Person B provided (in the xlsx file) into the master file (xlsm with the coding). The code dumps all the data from the xlsx, into the "Import" sheet of the xlsm file. It then runs the code above to copy across the relevant data from "import" to "master" sheet, both the import sheet and master sheet have the same conditional formatting rules on them, however when the code copies across the data from "Import" to "Master", the conditional formatting rules on the "master" sheet now shows the #REF! errors...

I think your right though in that when I delete row 9 in the "Import" sheet, it destroys the conditional formatting on the "Import" sheet, and then when it copies across to "Master", it also copies the now destroyed conditional formatting from "Import" to "Master"

I'll redo the code so that it doesn't delete rows 1 - 17 on the "Import" sheet and see if that changes it.