colour coding cell based on value of another

Hi,

I'm trying to colour code data contained in a report but struggling to achieve it.

The code below works ok but contains a colour coding section at the bottom which produces an error message 13 - type mismatch

I want to colour cells F,G & L for each row based on the value of AR on that row - less than 0 is red, 0-7 is yellow and 8> is green.

Would anyone be able to help please?

Sub copyData()
    Dim WB1 As Workbook
    Dim rCl As Range
     Dim lngLastRow As Long
    With Application
        .ScreenUpdating = False    'hide actions
        'open database
        
       Set WB1 = Workbooks.Open("C:\Users\Stephen\Documents\TSR1 OQ51 SS7.xls")
        With WB1
            'ThisWorkbook.Sheets("DATA").Rows("1:5").RowHeight = 30
            ThisWorkbook.Sheets("DATA").Columns("A:E").ColumnWidth = 12
            ThisWorkbook.Sheets("DATA").Columns("F:G").ColumnWidth = 30
            ThisWorkbook.Sheets("DATA").Range("A1:AZ5000").Clear
            'copy column A
            .Sheets("Combined").Range("A1:A5000").Copy
            ThisWorkbook.Sheets("DATA").Range("A1").PasteSpecial Paste:=xlPasteValues
            'copy column C
            .Sheets("Combined").Range("C1:C5000").Copy
            ThisWorkbook.Sheets("DATA").Range("D1").PasteSpecial Paste:=xlPasteValues
            'copy column D
            '.Sheets("Combined").Range("D1:D5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("G1").PasteSpecial Paste:=xlPasteValues
            'copy column E
            '.Sheets("Combined").Range("E1:E5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("H1").PasteSpecial Paste:=xlPasteValues
            'copy column F
            .Sheets("Combined").Range("F1:F5000").Copy
            ThisWorkbook.Sheets("DATA").Range("F1").PasteSpecial Paste:=xlPasteValues
            'copy column G
            .Sheets("Combined").Range("G1:G5000").Copy
            ThisWorkbook.Sheets("DATA").Range("G1").PasteSpecial Paste:=xlPasteValues
            'copy column H
            .Sheets("Combined").Range("H1:H5000").Copy
            ThisWorkbook.Sheets("DATA").Range("AA1").PasteSpecial Paste:=xlPasteValues
            'copy column I
            .Sheets("Combined").Range("I1:I5000").Copy
            ThisWorkbook.Sheets("DATA").Range("H1").PasteSpecial Paste:=xlPasteValues
            'copy column J
            .Sheets("Combined").Range("J1:J5000").Copy
            ThisWorkbook.Sheets("DATA").Range("I1").PasteSpecial Paste:=xlPasteValues
            'copy column K
            .Sheets("Combined").Range("K1:K5000").Copy
            ThisWorkbook.Sheets("DATA").Range("J1").PasteSpecial Paste:=xlPasteValues
            'copy column L
            '.Sheets("Combined").Range("L1:L5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("N1").PasteSpecial Paste:=xlPasteValues
            'copy column M
            '.Sheets("Combined").Range("M1:M5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("A1").PasteSpecial Paste:=xlPasteValues
            'copy column N
            .Sheets("Combined").Range("N1:N5000").Copy
            ThisWorkbook.Sheets("DATA").Range("M1").PasteSpecial Paste:=xlPasteValues
            'copy column O
            .Sheets("Combined").Range("O1:O5000").Copy
            ThisWorkbook.Sheets("DATA").Range("C1").PasteSpecial Paste:=xlPasteValues
            'copy column P
            .Sheets("Combined").Range("P1:P5000").Copy
            ThisWorkbook.Sheets("DATA").Range("AF1").PasteSpecial Paste:=xlPasteValues
            'copy column Q (date not used)
            '.Sheets("Combined").Range("Q1:Q5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("H1").PasteSpecial Paste:=xlPasteValues
            'copy column R (date not used)
            '.Sheets("Combined").Range("R1:R5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("N1").PasteSpecial Paste:=xlPasteValues
            'copy column S
            '.Sheets("Combined").Range("S1:S5000").Copy
            'ThisWorkbook.Sheets("DATA").Range("A1").PasteSpecial Paste:=xlPasteValues
        End With
        'restore screen updating
        .ScreenUpdating = True
        .CutCopyMode = False
        WB1.Close False
    End With
        
        '       Dates
        lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Range("K1:L1").Value = Array("Reqd Date", "Fcast Date")
        Range("B1").Value = Array("S/S No")
        Range("E1").Value = Array("OIS No")
        'Range("L2:L" & lngLastRow).Formula = "=DATEVALUE(AF2)"
         
        With Range("L2:L" & lngLastRow)
        .Formula = "=VALUE(AF2)"
        .NumberFormat = "dd/mm/yyyy"
        End With
        
        '       Concatenate
        With Range("AJ2:AJ" & lngLastRow)
        .Formula = "=CONCATENATE(A2,D2)"
        End With
                
        '       Extracting OIS Number
        With Range("AB2:AB" & lngLastRow)
        .Formula = "=RIGHT(AA2,27)"
        End With
        With Range("AC2:AC" & lngLastRow)
        .Formula = "=LEFT(AB2,4)"
        End With
        With Range("E2:E" & lngLastRow)
        .Formula = "=TEXT(AC2,1)"
        End With
                    
        '       Look up Ship Set No
        With Range("B2:B" & lngLastRow)
        .Formula = "=VLOOKUP(A2,'Tables'!$A$2:$B$150,2,FALSE)"
        End With
        
        '       Look up OIS Table Column no
        With Range("AN2:AN" & lngLastRow)
        .Formula = "=VLOOKUP(E2,'Tables'!$D$2:$E$35,2,FALSE)"
        End With
                          
        '       Look up Reqd Date
        With Range("K2:K" & lngLastRow)
        .Formula = "=VLOOKUP(AJ2,'Tables'!$J$2:$AN$500,AN2,FALSE)"
        .NumberFormat = "dd/mm/yyyy"
        End With
        
        '       Calculate data difference
        With Range("AR2:AR" & lngLastRow)
        .Formula = "=VALUE(K2-L2)"
        End With
   
        '       Colour Coding
        For Each rCl In Range("Z2:Z" & lngLastRow)
        'If rCl.Value = "1" Then rCl.Offset(0, 6).Value = "OK"
        'If rCl.Value = "<7" Then rCl.Interior.Color = RGB(255, 255, 0)
        If rCl.Value = "1" Then rCl.Interior.Color = RGB(255, 255, 0)
        Next rCl
        
        '       Set Value of NON OIS Parts to START
        For Each rCl In Range("E2:E" & lngLastRow)
        If rCl.Value = "1" Then rCl.Offset(0, 0).Value = "START"
        Next rCl
   
End Sub

Thanks,

Louise