Call macro pending on value

[Emoncada]

I have 5 status cells with a validation. I would a cell to tell me what the updated status is.

example

Cells
J11
J15
J19
J23
J27

If J27 = "" Then Go To J23

Else If J27 <> "" Then
If J27 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

Else ("AA1") = "WIP"

If J23 = "" Then Go To J19

Else If J23 <> "" Then
If J23 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

Else ("AA1") = "WIP"

If J19 = "" Then Go To J15

Else If J19 <> "" Then
If J19 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

Else ("AA1") = "WIP"

And so on until J11 At some point it should find something and then when Saved it will look at Cell AA1 and know the latest update.

[Bob Phillips]

[vba]

Dim TestCell As Range
Dim i As Long

For i = 27 To 11 Step -4

If Cells(i, "J").Value <> "" Then

Set TestCell = Cells(i, "J")
Exit For
End If
Next i

If TestCell Is Nothing Then

Range("AA1").Value = "WIP"
ElseIf TestCell.Value = "AVAILABLE" Or _
TestCell.Value = "RETIRE" Or _
TestCell.Value = "SHIPPING" Then

Range("AA1").Value = TestCell.Value
Else

Range("AA1").Value = "WIP"
End If
[/vba]