I need to use the Change Event for the following

Find the cell that changed on worsheet1 (easy (target.address))

Find the formula in worksheet2, column B that is referencing the
target.address in worksheet1.
store the time in column c on worksheet2 on the same row as the
formula

I've been chasing my tale on this and appreciate any and all feedack

THANKS

Private Sub Worksheet_Change(ByVal Target As Range)
' Intercept a change event on the form
MsgBox "Range " & Target.Address & " was changed"
'
' for example - Form B4 is referenced by B11 in the upload
spreadsheet
' the formula in B11 is '=Form!$B$4
' Form F4 is referenced by B23 in the upload
spreadsheet (=Form!$F$23)
'
' use absolute formulas

' store the date (now()) in the column E of the same row with the
formula in the upload sheet
'store the word 'changed' in column F in the upload sheet
' for example, E and F of row 11 in upload ... when Form B4
changes
End Sub

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim lrow As Integer
Dim ChangeAddress As String
'Address that was changed
ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
Target.Column & "*"
Debug.Print ChangeAddress

lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Debug.Print lrow
Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)

For Each r In myRange
Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
ChangeAddress
If Not IsEmpty(r) Then
If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
Debug.Print "Formula links to changed cell at address " &
r.Address
r.Offset(0, 4).Value = Now
r.Offset(0, 5).Value = "Changed"
End If
End If
Next r
End Sub



"Sharon" wrote:

> I need to use the Change Event for the following
>
> Find the cell that changed on worsheet1 (easy (target.address))
>
> Find the formula in worksheet2, column B that is referencing the
> target.address in worksheet1.
> store the time in column c on worksheet2 on the same row as the
> formula
>
> I've been chasing my tale on this and appreciate any and all feedack
>
> THANKS
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> ' Intercept a change event on the form
> MsgBox "Range " & Target.Address & " was changed"
> '
> ' for example - Form B4 is referenced by B11 in the upload
> spreadsheet
> ' the formula in B11 is '=Form!$B$4
> ' Form F4 is referenced by B23 in the upload
> spreadsheet (=Form!$F$23)
> '
> ' use absolute formulas
>
> ' store the date (now()) in the column E of the same row with the
> formula in the upload sheet
> 'store the word 'changed' in column F in the upload sheet
> ' for example, E and F of row 11 in upload ... when Form B4
> changes
> End Sub
>
>

Another suggestion you may be able to work with. Note that you requested to
search column B and put the time in column C, but your code comments indicate
you want the time in Column E. I used B and C.

You will need to change the worksheet name for rngSearch to whatever your
sheet is actually named. Watch for word wrap and be sure to back up before
trying.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCriteria As String
Dim rngSearch As Range
Dim rngFound As Range

strCriteria = Replace(Target.Address(True, True, xlA1, True), _
"[" & ThisWorkbook.Name & "]", "", 1, 1, vbTextCompare)
Set rngSearch = Sheets("Sheet2").Columns(2) '<<<CHANGE

With rngSearch
Set rngFound = .Find( _
What:=strCriteria, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngFound Is Nothing Then
With rngFound.Parent.Range("C" & rngFound.Row)
.Value = Now
.NumberFormat = """Changed ""m/d/yyyy h:mm:ss AM/PM"
End With
End If

End Sub



"Sharon" wrote:

> I need to use the Change Event for the following
>
> Find the cell that changed on worsheet1 (easy (target.address))
>
> Find the formula in worksheet2, column B that is referencing the
> target.address in worksheet1.
> store the time in column c on worksheet2 on the same row as the
> formula
>
> I've been chasing my tale on this and appreciate any and all feedack
>
> THANKS
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> ' Intercept a change event on the form
> MsgBox "Range " & Target.Address & " was changed"
> '
> ' for example - Form B4 is referenced by B11 in the upload
> spreadsheet
> ' the formula in B11 is '=Form!$B$4
> ' Form F4 is referenced by B23 in the upload
> spreadsheet (=Form!$F$23)
> '
> ' use absolute formulas
>
> ' store the date (now()) in the column E of the same row with the
> formula in the upload sheet
> 'store the word 'changed' in column F in the upload sheet
> ' for example, E and F of row 11 in upload ... when Form B4
> changes
> End Sub
>
>

On Apr 15, 7:32 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> Try this
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim myRange As Range
> Dim lrow As Integer
> Dim ChangeAddress As String
> 'Address that was changed
> ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
> Target.Column & "*"
> Debug.Print ChangeAddress
>
> lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
> Debug.Print lrow
> Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)
>
> For Each r In myRange
> Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
> ChangeAddress
> If Not IsEmpty(r) Then
> If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
> Debug.Print "Formula links to changed cell at address " &
> r.Address
> r.Offset(0, 4).Value = Now
> r.Offset(0, 5).Value = "Changed"
> End If
> End If
> Next r
> End Sub
>
>
>
> "Sharon" wrote:
> > I need to use the Change Event for the following
>
> > Find the cell that changed on worsheet1 (easy (target.address))
>
> > Find the formula in worksheet2, column B that is referencing the
> > target.address in worksheet1.
> > store the time in column c on worksheet2 on the same row as the
> > formula
>
> > I've been chasing my tale on this and appreciate any and all feedack
>
> > THANKS
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > ' Intercept a change event on the form
> > MsgBox "Range " & Target.Address & " was changed"
> > '
> > ' for example - Form B4 is referenced by B11 in the upload
> > spreadsheet
> > ' the formula in B11 is '=Form!$B$4
> > ' Form F4 is referenced by B23 in the upload
> > spreadsheet (=Form!$F$23)
> > '
> > ' use absolute formulas
>
> > ' store the date (now()) in the column E of the same row with the
> > formula in the upload sheet
> > 'store the word 'changed' in column F in the upload sheet
> > ' for example, E and F of row 11 in upload ... when Form B4
> > changes
> > End Sub- Hide quoted text -
>
> - Show quoted text -

I must be missing something truly elementary. I've tried both (solving
the word wrap and changing the sheet name). No errors but it doesn't
find a match and it should. How can I post my workbook?

I've noticed that if you don't put a space between the underscore and the
last character of the line, you'll get an error with the line wrap. Is this
what you're talking about?

Why don't you step through it and see where it croaks. Are you sure you've
got all the spaces in the sheet names?

"Sharon" wrote:

> On Apr 15, 7:32 pm, Barb Reinhardt
> <BarbReinha...@discussions.microsoft.com> wrote:
> > Try this
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim myRange As Range
> > Dim lrow As Integer
> > Dim ChangeAddress As String
> > 'Address that was changed
> > ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
> > Target.Column & "*"
> > Debug.Print ChangeAddress
> >
> > lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
> > Debug.Print lrow
> > Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)
> >
> > For Each r In myRange
> > Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
> > ChangeAddress
> > If Not IsEmpty(r) Then
> > If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
> > Debug.Print "Formula links to changed cell at address " &
> > r.Address
> > r.Offset(0, 4).Value = Now
> > r.Offset(0, 5).Value = "Changed"
> > End If
> > End If
> > Next r
> > End Sub
> >
> >
> >
> > "Sharon" wrote:
> > > I need to use the Change Event for the following
> >
> > > Find the cell that changed on worsheet1 (easy (target.address))
> >
> > > Find the formula in worksheet2, column B that is referencing the
> > > target.address in worksheet1.
> > > store the time in column c on worksheet2 on the same row as the
> > > formula
> >
> > > I've been chasing my tale on this and appreciate any and all feedack
> >
> > > THANKS
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > ' Intercept a change event on the form
> > > MsgBox "Range " & Target.Address & " was changed"
> > > '
> > > ' for example - Form B4 is referenced by B11 in the upload
> > > spreadsheet
> > > ' the formula in B11 is '=Form!$B$4
> > > ' Form F4 is referenced by B23 in the upload
> > > spreadsheet (=Form!$F$23)
> > > '
> > > ' use absolute formulas
> >
> > > ' store the date (now()) in the column E of the same row with the
> > > formula in the upload sheet
> > > 'store the word 'changed' in column F in the upload sheet
> > > ' for example, E and F of row 11 in upload ... when Form B4
> > > changes
> > > End Sub- Hide quoted text -
> >
> > - Show quoted text -
>
> I must be missing something truly elementary. I've tried both (solving
> the word wrap and changing the sheet name). No errors but it doesn't
> find a match and it should. How can I post my workbook?
>
>

On Apr 16, 4:12 am, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:
> I've noticed that if you don't put a space between the underscore and the
> last character of the line, you'll get an error with the line wrap. Is this
> what you're talking about?
>
> Why don't you step through it and see where it croaks. Are you sure you've
> got all the spaces in the sheet names?
>
>
>
> "Sharon" wrote:
> > On Apr 15, 7:32 pm, Barb Reinhardt
> > <BarbReinha...@discussions.microsoft.com> wrote:
> > > Try this
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim myRange As Range
> > > Dim lrow As Integer
> > > Dim ChangeAddress As String
> > > 'Address that was changed
> > > ChangeAddress = "*" & Target.Parent.Name & "!R" & Target.Row & "C" &
> > > Target.Column & "*"
> > > Debug.Print ChangeAddress
>
> > > lrow = Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
> > > Debug.Print lrow
> > > Set myRange = Worksheets("Sheet2").Cells(1, "B").Resize(lrow, 1)
>
> > > For Each r In myRange
> > > Debug.Print r.Address, myRange.Parent.Name & "!" & r.FormulaR1C1,
> > > ChangeAddress
> > > If Not IsEmpty(r) Then
> > > If myRange.Parent.Name & "!" & r.FormulaR1C1 Like ChangeAddress Then
> > > Debug.Print "Formula links to changed cell at address " &
> > > r.Address
> > > r.Offset(0, 4).Value = Now
> > > r.Offset(0, 5).Value = "Changed"
> > > End If
> > > End If
> > > Next r
> > > End Sub
>
> > > "Sharon" wrote:
> > > > I need to use theChangeEventfor the following
>
> > > > Find the cell that changed on worsheet1 (easy (target.address))
>
> > > > Find the formula in worksheet2, column B that is referencing the
> > > > target.address in worksheet1.
> > > > store the time in column c on worksheet2 on the same row as the
> > > > formula
>
> > > > I've been chasing my tale on this and appreciate any and all feedack
>
> > > > THANKS
>
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > ' Intercept achangeeventon the form
> > > > MsgBox "Range " & Target.Address & " was changed"
> > > > '
> > > > ' for example - Form B4 is referenced by B11 in the upload
> > > > spreadsheet
> > > > ' the formula in B11 is '=Form!$B$4
> > > > ' Form F4 is referenced by B23 in the upload
> > > > spreadsheet (=Form!$F$23)
> > > > '
> > > > ' use absolute formulas
>
> > > > ' store the date (now()) in the column E of the same row with the
> > > > formula in the upload sheet
> > > > 'store the word 'changed' in column F in the upload sheet
> > > > ' for example, E and F of row 11 in upload ... when Form B4
> > > > changes
> > > > End Sub- Hide quoted text -
>
> > > - Show quoted text -
>
> > I must be missing something truly elementary. I've tried both (solving
> > the word wrap and changing the sheet name). No errors but it doesn't
> > find a match and it should. How can I post my workbook?- Hide quoted text -
>
> - Show quoted text -

Thank you for your thorough response. It turns out that as the module
executed, Excel decided to go off an execute another module when it
was only 1/2 way through the change event module. I removed the other
module and now excel happily completes and I have the values I need.
Whew! I ended up calling MrExcel. They were very professional.