auto updating cell values

[dok112]

Hello all,

I am having a bit of a problem trying to figure out how to write thi
code.
Basically, I have in Cell A1 a value (ie 100) and cell A2 is a dro
down
validation box with 2 possible responses (YES & NO). What I need is
code,
that will automatically update the value of A1 if cell A2 is set t
YES.
It's a count of what is left.

ie. A1 = 100 and you select YES for A2, then A1 needs to update to 9

(A1.value - 1), but if A2 is NO, then no change is made to A1.

Any help provided will be greatly appreciated!!

Thoma

 

[Guest]

Thomas,
Paste the following code in the code pane for the worksheet (press ALT+F11
in Excel and click on Sheet1 in the VB Project Explorer):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B1").Address Then
If Target.Value = "YES" Then
If Not Range("A1") <= 0 Then Range("A1") = Range("A1").Value - 1
End If
End If
End Sub

This should do it.
Good luck.
E. Froma