Automated Macro

[akemeny]

I have a formula in column A6 that relates to W6. When W6 is changed to
Reject, the formula generates the appeal level. At which point my macro is
supposed to kick in and highlight the row the correct color, but it's
doesn't. What is the code that I can use to make all actions in a macro
happen automatically?

 

[Mike H]

Hi,

We need to see you macro but if it's a worksheet_Change looking at A6 then a
change in A6 as a result of calculation won't call the worksheet change event
code. You would need to look as W6 which will call the event if it's value is
manually changed.

Mike

 

[akemeny]

All the formula does is auto change the word that is in the cell A6. The
macro reads cell A6 and changes the color, but it will only change if I click
on cell A6.

This is the Macro that I have as of right now that changes the color:

Sub colortotalrow()
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("FI", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 45
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("RAC", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 36
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("ALJ", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 10
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("QIC", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 46
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("Closed", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 34
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
With Worksheets("Apr 5 - 2042253").Range("a6:a82")
Set A = .Find("na", LookIn:=xlValues)
If Not A Is Nothing Then
firstaddress = A.Address
Do
Range("a:bn").Rows(A.Row).Interior.ColorIndex = 35
Set A = .FindNext(A)
Loop While Not A Is Nothing _
And A.Address <> firstaddress
End If
End With
End Sub

So all I need is a macro that will run in the background and auto run the
macro above.

 

[Gord Dibben]

Since the value in A6 is formula-generated you can use worksheet_calculate
event.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A6")
If .Value = appeal level Then

'call macro to color row

End If
End With
stoppit:
Application.EnableEvents = True
End Sub