Tri state macro

[Aaron]

Hi,

I have got this code from another thread in my search for a solution to
a cell (f27) firing a macro.

It works well, but I cant get the third condition to work IE Call
Rockwell Both.

Perhaps it can be done a totally different way also.

I want cell f27 changing to fire a macro A

Cell k27 changing to fire a macro B

and if both cells change to fire macro C


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("f27")) Is Nothing Then
For Each myCell In Intersect(Target, Range("f27"))
If myCell.Value < 90 Then
Call Rockwell_AddC
End If
If myCell.Value = " " Then
Call Rockwell_RemoveC
End If
If k27 < 90 And f27 < 90 Then
Call Rockwell_Both
End If
Next myCell
End If
End Sub

Any advice greatly appreciated.

Cheers,

Aaron.

 

[Bob Phillips]

Change

If k27 < 90 And f27 < 90 Then

to

If Target("K27").Value < 90 And Target.Value < 90 Then


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Don Guillett]

maybe??? be sure you use caps for your $A instead of $a

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target < 90 Then a
If Target.Address = "$A$11" And Target < 90 And Cells(10, "a") < 90 Then
c
ElseIf Target.Address = "$A$11" And Target < 90 Then
b
End If
End Sub

Sub a()
MsgBox "a"
End Sub
Sub b()
MsgBox "b"
End Sub
Sub c()
MsgBox "c"
End Sub

 

[Aaron]

Hi Bob,

The line when entered in place of what I had, just generates an error.

Can you elaborate any more on what I could change?

Cheers,

Aaron.

 

[Aaron]

Hi Don,

It is not quite working right. This is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$27" And Target < 90 Then a
If Target.Address = "$F$27" And Target < 90 And Cells(10, "a") < 90
Then
c
ElseIf Target.Address = "$K$27" And Target < 90 Then
b
End If
End Sub

By changing around the second If Target line string it just changes the
msg box popup. It is not giving me the 3 state macro trigger I need. I
also cant quite understand the And Cells(10, "a') part either.

Can you elaborate more please?

Cheers,

Aaron.

 

[Don Guillett]

cells(10,"a") is the same as range("a10") so you would want cells(27,"k").
It pays to think about these things. By looking at my tested solution you
could have figured this out even if not familiar with cells.. Try it again,
without your changes.

 

[Aaron]

Hi Don,

It works well thanks.

Cheers,

Aaron.

cells(10,"a") is the same as range("a10") so you would want cells(27,"k").
It pays to think about these things. By looking at my tested solution you
could have figured this out even if not familiar with cells.. Try it again,
without your changes.

 

[Don Guillett]

glad to help