• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Conditional formatting


New Member
Hi Excel gurus,

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

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...

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:
    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)
    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...

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
Last edited by a moderator: