Conditional Formating

[markvdh]

Hi Guys
Can anyone help? I need some VBA code for a timeline. Specifically, I
need to be able to identify both weekends and the current date along a
horizontal timeline, with sequential dates above the timeline. It would
be nice to colour the cells containing weekends in a grey and the
current date in yellow.
Thanks

 

[Tom Hutchins]

You don't need VBA to apply conditional formatting, but since you asked for
it, here is a macro which applies the formatting you requested. I recorded it
using cell C5. Change C5 throughout to one of the cells in your timeline. Run
the macro to format that cell, then use Format Painter to apply the same
condtional formatting to the whole timeline.

Sub ApplyCondFmt()
'Applies requested conditional formatting to cell C5.
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=TODAY()"
Selection.FormatConditions(1).Interior.ColorIndex = 36 'Light yellow
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(WEEKDAY(C5,1)=1, WEEKDAY(C5,1)=7)"
Selection.FormatConditions(2).Interior.ColorIndex = 15 'Light gray
End Sub

Hope this helps,

Hutch