Reducing the amount of CF code in a macro.

[Graham Pall]

So as I am fairly new to conditional formatting, I have another question. Currently I have CF set out to highlight in BOLD all lines that contain a date withing the next 14 days.

So I currently have the code below repeated 14 times with TODAY+1, TODAY +2, etc, etc. Is there anyway to combine this into less code?

Cells.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$M2=TODAY()"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Strikethrough = False
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

[JBeaucaire]

Change the formula to this, then you only need one entry:

=($M$2-TODAY())<15

[Graham Pall]

Hmm, doesn't seem to like that. Not sure why yet. Is the syntax definitely right?

Bracket after = and before $ correct? Do I need to remove the $ sign?

Sorry I can't be more helpful, still learning CF.

[Pete_UK]

What Jerry is suggesting is that your second line should read:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=($M2-TODAY())<15"

I've missed the $ off in front of the 2 as I suspect you want this formula to apply to rows from 2 to whatever you have selected.

Hope this helps.

Pete

[Graham Pall]

Hi Pete. Yes, that's what my line looks like, I didn't replace the whole line with just that piece of code!

So applying that to the sheet, the only date being bolded is down the bottom of the sheet, 23/12/2015. Hmmm.....

Todays date, tomorrows date etc is still in unbolded font.

However I have just noticed that if I manually change the date on one of the rows to within the next 14 days, it changes the cell above it to bold? Weird.

There is one conditional statement before that in the macro to bold anything previous to todays date, perhaps that is interfering?

Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$M2<TODAY()"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = -16776961
        .TintAndShade = 0