Macro

[Guest]

hello,
i have data sheet A1:E1000 now i want creat a macro, whenever B coulnm
has = Clear i want let macro run automatically and makes lock that row
A2:E2 like is as under:

A B C D E
Ok Pending Japan 10,500 Abc
Ok Clear Japan 5,000 Bcd

thanks .

 

[Guest]

Try this:

Sub eqClear()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Range("$B$2:$B" & lastRow)
If LCase(c) = "clear" Then
c.Offset(0, 1) = Japan
c.Offset(0, 2) = 5000
c.Offset(0, 3) = Bcd
End If
Next
End Sub

 

[Guest]

thank you for your posting, but actully what i wanted just B=Clear othe no
need make setting, hope you could understand now.

 

[Guest]

Still not sure what you are looking for. This is for worksheet
change and must be pasted in the worksheet code module.
Right click the sheet tab for the sheet that contains your data
and then select view code from the menu. Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
lr = Cells(Rows.Count, 2).End(xlUp).Row
If Not Intersect(Target, Columns("B")) Is Nothing Then
If LCase(Target.Value) = "clear" Then
Target.Offset(0, 1) = "Japan"
Target.Offset(0, 2) = 5000
Target.Offset(0, 3) = "Bcd"
End If
End If
End Sub

If a user types the word "Clear" into column B, then columns C, D and E
will be populated with the data in that you showed in your posting.