Clear contents of cell based on value of another cell

[STEVEMILLS04]

How do I use event driven VBA to clear the contents of the cell within range K3:K300 if the data in cell J is not equal to "Promotable"? Obviously, if cell J3 is not Promotable then cell K3 should be cleared, etc...

Anytime cell J is changed the VBA should check K.

I am not very good with VBA but tried a few things and failed.

Thank you in advance.​

 

[kevatarvind]

try below code

Sub Test()
Range("J3").Select
Do Until ActiveCell.Row = 300
If ActiveCell.Value = "Promotable" Then ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[STEVEMILLS04]

try below code

Sub Test()
Range("J3").Select
Do Until ActiveCell.Row = 300
If ActiveCell.Value = "Promotable" Then ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Kevatarvind,

I tried the code by pasting it to the sheet code but it did not work. Whenever J3 is changed to any value other than Promotable, cell K3 should be cleared. OF course, if J4 is changed, K4 should be cleared.

 

[kevatarvind]

what u need ?
you need that macro should run each time automatic if you changed the value ?

 

[kevatarvind]

i think you need code that run automatic so use below code on worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each Cell In Range("J3:J300")
If UCase(Cell) = "PROMOTABLE" Then Cell.Offset(0, 1).ClearContents
Next
End Sub