Missed Conditional Formatting After Running Macro

[Magdoulin]

Hi guys, how is everything?
Look, I need a quick consultancy how to sort this issue out.
First, please visit this Google Drive link to view the Excel file subject to discussion:
https://drive.google.com/open?id=1_u...c6E-NBM5lB02ob

It’s big file, that’s why I needed to put it in Google Drive.
Now, in two sheets in the file: 1) (Centre Name) Final Sheet & 2) Intraday, there is a hidden, G column is hidden and it has some conditional formatting.

When I run the macro, first, the conditional formatting in G column changes the way it works.

Second, when I copy the generated data range in (Centre Name) Final Sheet and paste it in Intraday sheet, it paste everything but G column, which is really strange.

How could I fix these two issues please?


This post was originally posted here but I got no answer
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1212494-missed-conditional-formatting-after-running-macro

 

[Michael M]

As we / I can't see the macro, I'd guess that the macro code makes some changes to column G by either reformatting, or data including formats getting pasted there. In either case macro code will overide any preexisting CF formats.

 

[Magdoulin]

Thank you for your quick reply.

The macro is saved in the file actually.

But I am putting it here for the referemce.

If you may have a look and advise how I could sort the issue out.

Option Explicit
Sub Delete_Imcomplete()
    ActiveSheet.Range("$B$2:$S$1002").AutoFilter Field:=8, Criteria1:=RGB(217, 217, 217), Operator:=xlFilterCellColor
    ActiveSheet.Range("$B$2:$S$1002").AutoFilter Field:=9, Criteria1:=RGB(214, 220, 228), Operator:=xlFilterCellColor
    ActiveSheet.Range("$B$2:$S$1002").AutoFilter Field:=11, Criteria1:=RGB(226, 239, 218), Operator:=xlFilterCellColor
    ActiveSheet.Range("$B$2:$S$1002").AutoFilter Field:=13, Criteria1:=RGB(255, 242, 204), Operator:=xlFilterCellColor
    ActiveSheet.Range("$B$2:$S$1002").AutoFilter Field:=15, Criteria1:=RGB(252, 228, 214), Operator:=xlFilterCellColor
 
Dim lastRow
lastRow = 10000
Dim iCntr As Long
    For iCntr = lastRow To 1 Step -1
        If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete
    Next
Cells(Application.Evaluate("MAX(IF(D1:d1002<>"""",ROW(d1:d1002)),0,1)"), "d").Select
End Sub