code to change font colour after certain date

[smokenack]

Hi,

I'm trying to write some code that upon opening the workbook will change the font colour of a certain range if a certain date has passed. I also want to stop users being able to change the font colour after the code has changed it.

The following

Sub MARKNUTS()
If (TODAY) > 39901 Then
Worksheets("Sheet1").Range("A1:Z500").Font.ColorIndex = 4
End If
End Sub

does nothing. As always I can't remember how to post code tags (sorry) and can't remember the upon opening bit.

thanks for looking

Nick

 

[lenze]

In the ThisWorkBook module. Right Click the excel icon left of "File" on the menu bar and choose "View Code"

Private Sub Workbook_Open()
If (TODAY) > 39901 Then
    Sheet1.Unprotect "password"
    Worksheets("Sheet1").Range("A1:Z500").Font.ColorIndex = 4
    Sheet1.Protect "password"
End If
End Sub

lenze

BTW: Regular Conditional Formatting should work, as it will override any manually introduced formatting.

 

[smokenack]

Thanks for that Lenze but still no luck. I've pasted the code into the ThisWorkbook module but nothing happens upon opening with or without the Sheet protected.

Nick

 

[smokenack]

Success! I tried (NOW) instead of (TODAY) and bingo!

Many thanks

Nick

 

[lenze]

I should have caught that!! TODAY is not a VBA function. You might, however, use DATE instead of NOW
lenze