Macro Help: Change Timestamp When Worksheet Range Changed

[ACF0303]

Greetings,

I am trying to have one cell (G5) populate with a timestamp when a change is made to any cells in a range (some are merged). The code below worked for a second, until it didn't. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F8:G33")) Is Nothing Then Exit Sub
Range("G5").Value = Now()
End Sub

*Getting a yellow arrow by the row starting with Range("G5")

Thanks!
Cathy

 

[AlphaFrog]

Did you password protect the worksheet?

What's the error description?

 

[ACF0303]

I did password protect the sheet.......that's it isn't it? I've also noted that the timestamp does not match what is on my computer.....same problem? Do I need code to unlock/relock the sheet?

 

[AlphaFrog]

Do I need code to unlock/relock the sheet?

Yes. Start there and see what happens.

 

[ACF0303]

Um....could you help me with that? I'm learning as I go along....no real VBA training.

 

[AlphaFrog]

Um....could you help me with that? I'm learning as I go along....no real VBA training.

Change the password to suit

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]If[/color] Intersect(Target, Range("F8:G33")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    Me.Unprotect Password:="secret"
    Range("G5").Value = Now
    Me.Protect Password:="secret"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Tip: you can get example code by recording the steps (unprotect sheet\protect sheet) in a macro, and then copy the relevant bits to your final macro. I do it all the time.

Tip2: highlight a command in the code (e.g. Protect) and press the F1 key to get help on that command; syntax, definition, example code.

 

[ACF0303]

Thank you so much! Works beautifully and the tips are very helpful.