Entry in One Cell Updates Another

[neil10365]

Firstly, thanks for any help anyone can give with this as it's doing my
head in at the moment.

Cell A1 contains a value, let's say 10. Cell B1 contains an IF formula
that says if user enters 10 in A1 then make cell B1 equal 20. The user
enters 10 in A1 and then instantly B1 shows 20.

I basically want to put this in VBA and not have a formula in B1, I can
do this but only in a way where I have to run the macro to make it
happen, I want it to happen instantly, the way it would happen if there
were the IF formula in the spreadsheet.

One last thing, the user will be able to enter a variety of codes into
A1 and I want B1 (using the VBA solution) to read the code entered and
display a pre-defined message in the B1 cell. (e.g user enters "Cat"
into A1 and B1 then says "Cat's love fish", likewise if they enter
"Dog" into A1, then B1 says "Dog's love bones", with a list of 10 or
more possible combinations is it better to use Select Case for this
and, if it is, could someone point me in the right direction?

Many thanks!


Neil

 

[Guest]

Use a sheet change event to run when the value of cell A1 changes eg:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$A$1" Then
Select Case UCase(Target.Value)
Case "DOG"
Range("B1").Value = "Dogs love bones"
Case "CAT"
Range("B1").Value = "Cats love fish"
Case 10
Range("B1").Value = 20

'other case statements here

Case Else 'case else optional
Range("B1").Value = ""
End Select
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right click the sheet tab, select View Code
and paste the code in there.

Hope this helps
Rowan

 

[Neil10365]

Rowan

This really does help and completely solves this one for me - Thanks
for your help and such fast response - greatly appreciated.

Neil

 

[Guest]

You're welcome!