message appears in adjacent column when name appears in column A

[snowy]

Hello
In my column A, I will have a varying list of names. Is there a way I could loop through list A and when it sees a specific name, print the message"please renew your account) in the adjacent cell in the B column.:).
Thankyou in advance
Snowy

[rollis13]

Paste this macro to the sheet's module. I'm assuming that you are inputting the specific name (not case sensitive) to search in cell C1.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fnd As Range
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
Set fnd = Range("A1:A" & lr).Find(What:=Range("C1").Value, LookIn:=xlValues, LookAt:=xlWhole)
If fnd Is Nothing Then
MsgBox Range("C1").Value & " not found!"
Else
Application.EnableEvents = False
fnd.Offset(0, 1) = "please renew your account"
Application.EnableEvents = True
End If
End Sub

[snowy]

Hi rollis 13,
That works great!! Thankyou :hi::hi::clap:
Snowy

[rollis13]

Thanks for the positive feedback :thumb, glad having been of some help.

[snb]

Use only target in event-procedures.
Avoid redundant VBA-code.
Avoid redundant variables.


Private Sub Worksheet_Change(ByVal Target As Range)
on error resume next
if target.address="$C$1" then columns(1).find(target,,,1).offset(,1)= "please renew your account"
if err.number<>0 then msgbox Target & " not found"
End Sub

[Tom Jones]

Use only target in event-procedures.
Avoid redundant VBA-code.
Avoid redundant variables.


Private Sub Worksheet_Change(ByVal Target As Range)
on error resume next
if target.address="$C$1" then columns(1).find(target,,2).offset(,1)= "please renew your account"
if err.number<>0 then msgbox Target & " not found"
End Sub

For every name in column A (if you put one of the name from column A, in C1) your code gave "NOT FOUND"

rollis13 code, work very well.

[georgiboy]

Avoid redundant variables. :banghead:

[rollis13]

@Tom Jones, no problem, @snb just had a typo, this works:
If Target.Address = "$C$1" Then Columns(1).Find(Target, , , 1).Offset(, 1) = "please renew your account"

[snb]

@rollis has a typo too @snd

[rollis13]

@snb, you were partially in the mirrow :rofl:, not any more:beerchug:.

[snb]

or down-under qus@