VBA Conditional formatting

[Roxhan]

Hi Excel gurus,

Hoping someone can help me with this VBA code problem...

Overview:
I am creating a 'Formatting' macro that will rearrange/organise my data, which I copy from another spreadsheet, every quarter.
I've been recording macros in small sections and slowly joining them all together in VBA, to create one uber macro button ha.
I have created conditional formatting rules via the 'front end', however when I tried to record those steps as a macro, the VBA module shows up blank, so I need to input directly into VBA...

Problem
I need to apply two conditional formatting rules to Range N6:N500

1. Highlight cells in N6:N500 if the date is within 2wks of today (pink fill, purple font). This part of VBA works, yay.
   1. Front end conditional format Rule used was "=AND(N6>TODAY(),N6<=(TODAY()+14))"
2. Highlight cells in N6:N500 if Column F6:F500 says 'Complete' or 'Cancelled' (Light grey fill, dark grey font). This is the part that doesn't work...
   1. Front end conditional format rule used, which worked, was "=OR($F6="Complete",$F6="Cancelled")"

(I think rule 2 needs to be above 1, but I can swap them around, see which order works correctly.)

Current code:

    Range("N6:N500").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(N6>TODAY(),N6<=(TODAY()+14))"
    Selection.FormatConditions(1).Interior.Color = RGB(248, 195, 240)
    Selection.FormatConditions(1).Font.Color = RGB(87, 0, 102)
        Range("N6:N500").Select
    Select Case ActiveSheet.Cells(N6, F6)
        Case "Complete", "Cancelled"
            Selection.FormatConditions(2).Interior.Color = RGB(206, 206, 206)
            Selection.FormatConditions(2).Font.Color = RGB(87, 0, 102)
    End Select

VBA doesn't like row 6 'Select Case ActiveSheet.Cells(N6,F6)'... I took a stab, but definitely not right syntax.
I originally tried duplicating the first rule and replacing the formula with the "=OR..' statement I used successfully in the front end. But I got an error, hence trying the 'case' route...

Request:
Can someone help me fix the second conditional formatting... AND is it possible to do this without the whole 'setup lines'? Here's an example I found online, but couldn't work it out...

Dim RRow As Long
N As Long
RRow = ActiveSheet.UsedRange.Rows.Count
For N = 1 To RRow

...as I don't know if those 'up front' setup lines will affect my overall macro, which is several pages long of different commands?

Many thanks for any help you can provide
Rox