Conditional macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

@Homeonthecouch - 15 Mar 2007 15:58 GMT

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?

Signature

Any help is as always appreciated
Thank You

Andrew

Reply to this Message

Dave Peterson - 15 Mar 2007 16:33 GMT

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: ...
...

> Hello,
> I have a sheet that I want to change daily. Each day has a colour to a cell
[quoted text clipped - 35 lines]
>
> Andrew

Signature

Dave Peterson

Reply to this Message

@Homeonthecouch - 15 Mar 2007 17:01 GMT

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

Andrew

> First, you could use =today() to just get the day (to drop the time).
>
[quoted text clipped - 50 lines]
>>
>> Andrew

Reply to this Message

@Homeonthecouch - 17 Mar 2007 11:37 GMT

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

> First, you could use =today() to just get the day (to drop the time).
>
[quoted text clipped - 50 lines]
>>
>> Andrew

Reply to this Message