flash cell colours from white to red and back again

[MarkyB]

When a check box is ticked, I would like to automatically change any whote
cells to red, throw up the MsgBox and then change the red back again to
white. When I call the macro I get a run-time error '438'. My code that I am
trying to use is:

Public Sub PriorityWarning()

With Sheets(1)
Cells.Select
If .BackColor = RGB(255, 255, 255) Then
.BackColor = RGB(255, 51, 0)
End If
MsgBox "Priority EPCON1!", vbOKOnly, "EPCON LEVEL"
If .BackColor = RGB(255, 51, 0) Then
.BackColor = RGB(255, 255, 255)
End If
End With

End Sub

Thanks,

Mark

 

[Joel]

Public Sub PriorityWarning()

Sheets(1).Activate
With ActiveCell
If .Interior.Color = RGB(255, 255, 255) Then
.Interior.Color = RGB(255, 51, 0)
End If
MsgBox "Priority EPCON1!", vbOKOnly, "EPCON LEVEL"
If .Interior.Color = RGB(255, 51, 0) Then
.Interior.Color = RGB(255, 255, 255)
End If
End With

End Sub

 

[MarkyB]

Sorry Joel nothing happens apart from showing the msgbox, but it has
eliminated the run-time error..

 

[Joel]

All that means is the present color of the cell is not the two choise you
have in the code. Color is a problem becuase RGB sets a number but color
returns a string. Try colorindex

Public Sub PriorityWarning()

Sheets(1).Activate
With ActiveCell
If .Interior.ColorIndex = xlNone Then
.Interior.ColorIndex = 3
End If
MsgBox "Priority EPCON1!", vbOKOnly, "EPCON LEVEL"
If .Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlNone
End If
End With

End Sub

 

[MarkyB]

Hi Joel, thanks for your help but still nothing happens. Still I have learnt
more. It was going to be a humurous aspect to a client brief that when they
request an urgent priority we would try to flash the colours and show a
msgbox. msgbox works fine!

 

[Joel]

It means that the current color of the cell is something other than None or
Red.
none will equal -4142 or red will equal 3

try executing this line and see which color is returned
MsgBox ("Current color is " & ActiveCell.Interior.ColorIndex)

 

[MarkyB]

Nope, still no change. Index is indeed -4142. Only thing to happen is the
msgbox.

 

[Joel]

Isn't the cell red when the message box appears? Then turns back to blank
after message box closes

 

[MarkyB]

Brilliant! Must have had another cell active that I couldn't see on screen.

Thanks a million Joel