I can't make the damned macro starts on I change cell A1 on Sheet "Calendar" please help

Posted by Ada on June 09, 2000 6:49 AM

I can not make the following macro make run when I change the date in A1. I have more sheets in workbook, one is called "Calendar". On this one I want to run a macro called "Calendar", but only after I change the date in A1. I tried to use in Auto_Open the code Application.OnEntry="Calendar" but it did not work. When I tried another code (forgot it) the macro run even when I change cells in other sheets. Please help.

Sub auto_open()
Application.OnEntry = "Calendar"
End Sub
Sub Calendar()
'
' Macro recorded 05/10/2000

'
For Each C In Range("b7:au7")

C.Offset(1, 0).Font.Bold = True 'RESET 01,02,03 offset(1,0)=range(b8:au8)unbold cells days # (ex. 01,02)
C.Offset(1, 0).Font.ColorIndex = 0 'reset to no color fonts in cells that have days # (ex. 01,02)
With C.Offset(1, 0).Interior 'yellow in 1,2,3... cells
.ColorIndex = 6
.Pattern = xlSolid
End With

C.Interior.ColorIndex = xlNone 'RESET MON,TUE,WED range(b7:au7) no color in cells that have days name (ex. Mon,Tue)
C.Font.Bold = False 'unbold cells that have days name (ex. Mon,Tue...)
C.Font.ColorIndex = 0 'font color set to automatic (blue)

If C.Value = 1 Then 'IF is SUN

With C.Offset(1, 0).Interior '1,2,3 fill days cells (Sundays) (ex. 1,2) with color (blue)
.ColorIndex = 8
.Pattern = xlSolid
End With

With C.Interior 'Mon,Tue,Wed fill days cells (Sundays) (ex.Sun)with color (blue)
.ColorIndex = 8
.Pattern = xlSolid
End With

C.Font.Bold=Tr

Posted by Ada on June 12, 0100 6:45 AM

Thanks Ryan, I tried but does not work

What do you mean normal module? In the same module where I have Calendar macro, before the Sub Calendar ? I tried but does not work.

If Union(Target, VRange).Address = VRange.Address Then Call Calendar End If Application.ScreenUpdating = True

Posted by Ryan on June 12, 0100 8:04 AM

Re: Thanks Ryan, I tried but does not work

If you open up the VBE and press Ctrl-R to make sure the projects window is open you can see different projects. In the file you are working on is a list of folders. In the Excel Objects folder there are sheets. Find the sheet that is named calendar(or whatever the name is that the calendar is on) and double click that. The code for Private Sub Worksheet_Change(ByVal Target As Excel.Range) goes in here. There is another folder under your project named Modules. Put the rest of your code in here. Hope this clears things up.

Posted by Ryan on June 11, 0100 2:46 PM

Put this code in the Module for the worksheet, not in a normal module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("A1")
Application.ScreenUpdating = False

If Union(Target, VRange).Address = VRange.Address Then
Call Calendar
End If
Application.ScreenUpdating = True
End Sub

You can put the code for "Calendar" in a normal module.

Hope this helps