Need help modifying Worksheet_Change script to work with a range of cells...

[Br3nt]

Hi,

I'm forcing a time stamp into F17 based on "Pass, Fail, or Untested[P14]" value in E17 using Worksheet_Change.

So far, the script works for cell F17, however, I would like it to work for the range of cells F17:F500 based on values in E17:E500.

How should I modify this script to work with the range of cells???

Thanks for your help!!
-Br3nt

Here's the code snippet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target = [E17] Then
If Target.Value = "Pass" Then
Range("F17").Select
ActiveCell.Value = Now
ElseIf Target.Value = "Fail" Then
Range("F17").Select
ActiveCell.Value = Now
Else
Range("F17").Select
ActiveCell.Value = [P14]
End If
End If
End Sub

 

[VoG]

Hello and welcome to MrExcel.

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("E17:E500")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Value
    Case "Pass", "Fail": Target.Offset(, 1).Value = Now
    Case Else: Target.Offset(, 1).Value = Range("P14").Value
End Select
Application.EnableEvents = True
End Sub

 

[Smitty]

Welcome to the Board!

Does this do what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("E17:E500")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value = "Pass" <SPAN style="color:#00007F">Or</SPAN> Target.Value = "Fail" <SPAN style="color:#00007F">Then</SPAN><br>                Target.Offset(, 1) = Now<br>            Else: Target.Offset(, 1) = Range("P14").Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

 

[Br3nt]

Thank you very much!! Both solutions above solve the problem!

-Br3nt