macro - type in value, fill other cells w/same value if....

[Sayre]

Col D has list of order #'s, starting in row 9. Col M is for COMMENTS about each order. Order #'s can appear in Col D more than once.

What I am looking for is a macro that will automatically detect if I have entered a new COMMENT in Col M, recognise the order # in adjacent Col D, and put my new comment in other cells in Col M if the SAME ORDER # is referenced in adjacent Col D.

Example:

worksheet starts with:
Col D Col M
1234 x
123 xyz
1234 zyx
4321 zyx
321 y
1234 yz

If I type the letter "Q" into Col M in a row adjacent to order # 1234, the result should be:
Col D Col M
1234 Q
123 xyz
1234 Q
4321 zyx
321 y
1234 Q

I think I would need to put this macro into a Worksheet Change event but beyond that I do not know what I need. UDF? Macro?

Hope the above makes sense, thanks for help in advance!

 

[Sayre]

OK, I do not know enough about 'Target' to understand why that does not work but that's OK. As much as I think I know about macros, there's still quite a bit I don't know.

Thanks a ton!

 

[Sayre]

One more thing on this: The macro does not always work when I have an autofilter on. If I am putting into a comment into a field and I have autofilters on (AND one of the order #'s happens to be hidden by that autofilter), then it does not work.

Any way around that? This sheet will be used by many who like to use autofilters to look at subsets of order data...

 

[jindon]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range, LastR As Long, myOrd, myValue
If Target.Column <> 13 Then Exit Sub
If Target.Row < 9 Then Exit Sub
If Target.Count > 1 Then
     With Application
          .EnableEvents = False
          .Undo
          .EnableEvents = True
     End With
     MsgBox "Can't change multiple cells at a time"
     Exit Sub
End If
myOrd = Target.Offset(,-9).Value
myValue = Target.Value
LastR = Range("d" & Rows.Count).End(xlUp).Row
Set a = Range("d9:d" & LastR).SpecialCells(12)
Application.EnableEvents = False
For Each r In rng
     If r.Value = myOrd Then r.Offset(,9).Value = myValue
Next
Application.EnableEvents = True
End Sub

 

[Sayre]

This version of the code is giving me the 'Object Required' run-time error again. Is getting stuck on "For Each r In rng"

Thanks for everything so far Jindon! If you cannot help further I can probably come up with some other way to look at subsets of data....

 

[Sayre]

This version of the code is giving me the 'Object Required' run-time error again. Is getting stuck on "For Each r In rng"

Thanks for everything so far Jindon! If you cannot help further I can probably come up with some other way to look at subsets of data....

 

[jindon]

post all your codes regarding this matter.
I'm totally lost what you are doing.

 

[Sayre]

Jindon! So glad to hear from you. I found a way around this problem for now but I could actually use your help on a much more urgent request I recently posted.

Titled: macro - show rows ONLY if two columns = each other AND......

Similar to this one but different enough that I cannot figure out how to do it. If you could take a look at that one and see if you could help, I would be happy to drop this one and just use my work around. Can you give it a look?