#1. You could unprotect and reprotect your worksheet right in the code.
#2. Since the ranges are mutually exclusive, you could check to see if you're
in one range and do what you want there.

I think I'd put the range addresses in a variable. I think it makes updates a
little easier:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myUpperRng As Range
Dim myProperRng As Range
Dim myDateTimeRng As Range

Set myUpperRng = Me.Range("$J$4,$BP$5,AP$7,$F$7,$BH$24")
Set myProperRng = Me.Range("$AC$4,$H$5,$H$41,$AW$4")
Set myDateTimeRng = Me.Range("AR60:BX60")

On Error GoTo ErrHandler:
Application.EnableEvents = False
Me.Unprotect Password:="hi"
With Target
If .Cells.Count 1 Then Exit Sub
If Not (Intersect(myUpperRng, .Cells) Is Nothing) Then
.Value = StrConv(.Value, vbUpperCase)
ElseIf Not (Intersect(myProperRng, .Cells) Is Nothing) Then
.Value = StrConv(.Value, vbProperCase)
ElseIf Not (Intersect(myDateTimeRng, .Cells) Is Nothing) Then
If IsEmpty(.Value) Then
.Offset(0, -43).ClearContents
Else
With .Offset(0, -43)
.NumberFormat = "dd mmm yy hh:mm"
.Value = Now
End With
End If
End If
End With

ErrHandler:
Me.Protect Password:="hi"
Application.EnableEvents = True

End Sub

===========
PS. One of the nice things about "Select Case" is that you can do things like:

Select Case Target(1).Address '<--- note the (1)
Case "$J$4", "$BP$5", "$AP$7", "$F$7", "$BH$24"
Target(1).Value = UCase(Target(1).Value)
Case "$AC$4", "$H$5", "$H$41", "$AW$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
End Select

=========
I also figured that since you were only checking the first cell in the target,
you might as well just check to see if there's only one cell being changed.
That might not be ok for you.



Tim wrote:

I am trying to use the time and date stamps worksheet event macro from
McGimpsey but running into a few problems.

1. The worksheet I'm using is protected and this code won't run when the
worksheet is protected.

2. I already have a Worksheet_Change function to format cells for upper and
proper case. Do I have to incorporate the time and date stamps worksheet
event macro into my change case macro, if so any suggestions would be greatly
appreciated. Here is a part of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
Application.EnableEvents = False
Select Case Target.Address
Case "$J$4"
Target(1).Value = UCase(Target(1).Value)
Case "$AC$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$5"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$41"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$AW$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$BP$5"
Target(1).Value = UCase(Target(1).Value)
Case "$AP$7"
Target(1).Value = UCase(Target(1).Value)
Case "$F$7"
Target(1).Value = UCase(Target(1).Value)
Case "$BH$24"
Target(1).Value = UCase(Target(1).Value)
End Select
CleanUp:
Application.EnableEvents = True
End Sub

3. I would like to use the date stamp function for multiple entries(A60 to
A82). Here is what I had for one.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("AR60:BX60"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -43).ClearContents
Else
With .Offset(0, -43)
.NumberFormat = "dd mmm yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

Sorry for the length of this post.

Tim

--

Dave Peterson