Change values in cell via VB

[siddharth_kale]

Hello,

I have a requirement wherein:

Values in A1 will be entered by users. So if in the adjacent column B1 the status is approved, the entered value stays.

Now the value entered in A2 is rejected in B2, then A2 should become blank (the highlighted one).

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>v9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Approved</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>v8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Rejected</TD></TR></TBODY></TABLE>

 

[Trevor G]

You could use something like an offset method

Sub RunCode1()
Range("B1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "Rejected" Then
ActiveCell.Offset(0, -1) = ""
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[siddharth_kale]

Hello,

Thanks for replying. Works perfect.

Is there any way by which if the code is executed as soon as the value "Rejected" is selected from the drop down.

The reason being I do not want to assign the macro to a button.

 

[SteveO59L]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Rejected" Then
Target.Offset(0, -1).ClearContents
End If

End Sub

 

[siddharth_kale]

Great, thanks. Still one problem. I am using the below code, which is contracdicting. I want that users should not be able to change the value when it is entered once. If accepted it stays and if rejected it becomes blank.

So now when rejected makes it blank the first piece of code puts it back. Is there a better to do this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

Application.EnableEvents = False
For Each c In Target
With Sheets("AprilCheck").Range(c.Address)
If .Value <> "" Then
c.Value = .Value
Else
.Value = c.Value
End If
End With
Next c
Application.EnableEvents = True

If Target.Value = "Rejected" Then
Target.Offset(0, -1).ClearContents
End If
End Sub