Hi guys, I'm new to the forum. I currently live in Anchorage, AK. I love messing around with excel but am still in the
learning phase. I took this vba from the common questions messages. And I'm trying to tweak it to become my main calendar at
work. I was wondering just 1 thing.
How do you black out the past dates?
Like today is september 14th,
tomorrow I want september 15th to be highlighted but all dates previous to september 15th to be black.
the code if it helps any one out. Thanks in advance for the help.
Dim lMonth As Long
Dim strMonth As String
Dim rStart As Range
Dim strAddress As String
Dim rCell As Range
Dim lDays As Long
Dim dDate As Date
'Add new sheet and format
ActiveWindow.DisplayGridlines = False
.ColumnWidth = 6#
.Font.Size = 8
'Create the Month headings
For lMonth = 1 To 4
Select Case lMonth
strMonth = "January"
Set rStart = Range("A1")
strMonth = "April"
Set rStart = Range("A8")
strMonth = "July"
Set rStart = Range("A15")
strMonth = "October"
Set rStart = Range("A22")
'Merge, AutoFill and align months
.Value = strMonth
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 6
.Font.Bold = True
'Pass ranges for months
For lMonth = 1 To 12
strAddress = Choose(lMonth, "A2:G7", "H2:N7", "O2:U7", _
"A9:G14", "H9:N14", "O9:U14", _
"A16:G21", "H16:N21", "O16:U21", _
"A23:G28", "H23:N28", "O23:U28")
lDays = 0
'Add dates to month range and format
For Each rCell In Range(strAddress)
lDays = lDays + 1
dDate = DateSerial(Year(Date), lMonth, lDays)
If Month(dDate) = lMonth Then ' It's a valid date
.Value = dDate
.NumberFormat = "ddd dd"
'add con formatting
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=TODAY()"
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 1
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines