Conditional macro

[@Homeonthecouch]

Hello,
I have a sheet that I want to change daily. Each day has a colour to a cell
for that day.
The day is obtained by =NOW() function reporting the day only.
I want to background colour the cell the day is reported in differently each
day.
Monday=yellow
Tuesday=blue
etc. etc.
I have this code to do the colours. (The colorindex numbers may not
represent the above colours but that I can work out later)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("L23")) Is Nothing Then Exit Sub

With Target
Select Case (.Value)
Case Is = "Monday": .Interior.ColorIndex = 1
Case Is = "Tuesday": .Interior.ColorIndex = 2
Case Is = "Wednesday": .Interior.ColorIndex = 3
Case Is = "Thursday": .Interior.ColorIndex = 4
Case Is = "Friday": .Interior.ColorIndex = 5
Case Is = "Saturday": .Interior.ColorIndex = 6
Case Is = "Sunday": .Interior.ColorIndex = 7
End Select
End With

End Sub

But it is seeing the cell as the formula.
What do I need to change to make it see the answer to the formula?

 

[Dave Peterson]

First, you could use =today() to just get the day (to drop the time).

Second, if you formatted that cell to show "Monday", "Tuesday", ...
you could use:
Select Case .Text

But maybe you could use:

select case weekday(.value)
case is vbMonday: ...
case is vbTuesday: ...
....

 

[@Homeonthecouch]

Many thanks Dave that worked even with my limited ability to understand


Andrew

 

[@Homeonthecouch]

Hi again,
I have just noticed that when I open the spreadsheet the cell with the
=today() command automatically updates but the macro that is dependant on
the answer does not update.
I have tried to use refresh F9 but that doesn't work. I can go into the cell
and hit enter and that then refreshes the colour assigned to the day.
Is there a way of doing this when the spreadsheet opens up? Or is that not
advisable?

Andrew