Macro problem

  • Thread starter Thread starter Peter Taylor
  • Start date Start date
P

Peter Taylor

Dear Members
Hello, my name is Peter Taylor, from Swan Hill, Australia.
Which leads me to my next question. I am trying to create a simple
macro that when this macro is selected (or even better automatically
done when Excel is loaded) of highlighting a cell (from a range say of
A1:A100) that has the current date (6/11/2004). I have a long column
of dates eg:
5/11/2004 Friday
6/11/2004 Saturday
7/11/2004 Sunday
8/11/2004 Monday
9/11/2004 Tuesday
10/11/2004 Wednesday
11/11/2004 Thursday
12/11/2004 Friday
13/11/2004 Saturday
14/11/2004 Sunday

So as each new day comes around the date column (the current date)
cell is highlighted. I have tried some different codes, but whatever I
try VBA doesn't like the Today() function, but strangely enough likes
the Now() function, only problem there is that Now() also has the
minutes, hours etc.
Here is an example of what I have been trying to do.

Sub CalculateNewDays()
'For Each cell_in_loop In Range("C349:C649")
'If cell_in_loop.Value = Today() Then
' With cell_in_loop.Offset(0, 0).Interior
' .ColorIndex = 11
' .Pattern = xlSolid
' End With
'Selection.Font.ColorIndex = 2
'End If
'Next
For Each cell In Selection
If cell.Value = today() Then cell.Font.Color = vbBlue
Next cell
End Sub

Any help or assistance would be greatly appreciated.

Regards Peter Taylor, Australia
 
Hi Peter,

Today() is a worksheet function and will not work with VBA.
Replace Today() with Date.
"Date" is a VBA function that returns the system date.

Regards,
Jim Cone
San Francisco, CA
 
Hi Peter

(where's swan hill - i'm in perth)

if you place the following code in the ThisWorkbook module (in the VBE
window - top right hand side of screen in the project explorer you should
see ThisWorkbook just above where you see the module sheet containing the
current code module) then the code will run when you open the workbook.

Private Sub Workbook_Open()
For Each cell_in_loop In Range("a1:a100")
If Format(cell_in_loop.Value, "dd/mm/yy") = Format(Now, "dd/mm/yy") Then
With cell_in_loop.Offset(0, 0)
.Interior.ColorIndex = 11
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
Exit Sub
End With
End If
Next
End Sub

Cheers
JulieD
 
Dear Members
e-mail me personally at (e-mail address removed)
Whilst this seems to work for the current date (thanks again), can I
trap the previous dates as I have tried below (with no
success)?Because it will not be every day that I would be opening this
worksheet, so when I do open up this worksheet it will format, not
only the current date, but also any previous days as well?

Private Sub Workbook_Open()
For Each cell_in_loop In Range("c349:c900")
If Format(cell_in_loop.Value, "dd/mm/yy") = Format(Now,
"dd/mm/yy") Or Format(cell_in_loop.Value, "dd/mm/yy") < Format(Now,
"dd/mm/yy") Then
With cell_in_loop.Offset(0, 0)
.Interior.ColorIndex = 1
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
Exit Sub
End With
End If
Next
End Sub

Cheers Peter Taylor
 
Hi Peter

i'm now confused - do you just want to highlight the current date or
previous dates as well? if it is just the current date then my code should
work, and to "remove" previous day's highlighting you just need to put a
couple of lines in to select the whole column & reformat it before
highlighting the current day.

However, if you want to highlight the current date & all previous dates then
howabout
Private Sub Workbook_Open()
For Each cell_in_loop In Range("c349:c900")
If cell_in_loop.Value <= Date Then
With cell_in_loop.Offset(0, 0)
.Interior.ColorIndex = 11
.Interior.Pattern = xlSolid
.Font.ColorIndex = 2
End With
End If
Next
End Sub

Cheers
JulieD
 
Back
Top