Vba experts: Need help with date and time stamp being static

[bamaisgreat]

Thanks for looking the code below is what Im currently using. I need to modify this so it does not look in the entire row.
Example: instead of it looking in column 3 and 20 i need to look at column 3 and 20 down to row 25 starting with row 4.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        Application.EnableEvents = False
        Cells(Target.Row, 20).Value = Date + Time
        Application.EnableEvents = True
        End If
        
End Sub

 

[p45cal]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$C$4:$C$25")) Is Nothing Then
  Application.EnableEvents = False
  Cells(Target.Row, 20).Value = Date + Time
  Application.EnableEvents = True
End If
End Sub

 

[bamaisgreat]

Thanks p45cal, I have run into another issue with it. I have this in a module

Sub savee()
ActiveSheet.Unprotect
Dim cell As Range
For Each cell In Range("A4:E22")
If Not cell.HasFormula Then
cell.Value = UCase(cell.Value)
End If
Next cell

For Each cell In Range("G4:K22")
If Not cell.HasFormula Then
cell.Value = UCase(cell.Value)
End If
Next cell



Range("h4").Select
ActiveSheet.Protect
ActiveWorkbook.Save
End Sub

and when it runs it changes all the dates and times in the selected dells to current and fills in the ones that were blank. Any suggestions ?

 

[p45cal]

Range("A4:E22") includes the cells C4:C25, perhaps try putting Application.enableevents = False before the line For Each cell In Range("A4:E22") and Application.enableevents = true after the line Next Cell:

Sub savee()
    ActiveSheet.Unprotect
Dim cell As Range
    [COLOR=#ff0000]Application.enableevents = False[/COLOR]
    For Each cell In Range("A4:E22")
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    [COLOR=#ff0000]Application.enableevents = True[/COLOR]

     For Each cell In Range("G4:K22")
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    
    Range("h4").Select
    ActiveSheet.Protect
    ActiveWorkbook.Save
End Sub

 

[bamaisgreat]

Thanks that did it

 

[bamaisgreat]

P45cal thanks for all your help. I have one other issue. The code below is what Im using.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$C$4:$C$22")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 20).Value = Date + Time
Application.EnableEvents = True
End If
End Sub

Is there away if someone enters data in say C4 and then they delete that data that the date and time that is in row 20 will go away also. Thanks

 

[p45cal]

Is there away if someone enters data in say C4 and then they delete that data that the date and time that is in row 20 will go away also.

Try changing:

Cells(Target.Row, 20).Value = Date + Time

to:

  If Len(Target.Value) > 0 Then Cells(Target.Row, 20).Value = Date + Time Else Cells(Target.Row, 20).Value = ""

 

[bamaisgreat]

ok that worked great. The cells are protected, is there away to get around that.

 

[bamaisgreat]

Thanks Wolf, here is a link of one I use to test code.
https://skydrive.live.com/redir?resid=BC39913B0562FDC9!861&authkey=!AJIjGaT3kRS5ZYw

 

[p45cal]

have:
Me.Protect userinterfaceonly:=True
in the change event before it tries to change anything.

However, rather than put this in the sheet's change event which will have it executed a zillion times, you could put it into the workbook open event but you need to tell it which sheet, so:
Sheets("TheNameOfTheSheetHere").Protect userinterfaceonly:=True

Wolf? Who he?