ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   flash cell colours from white to red and back again (https://www.excelbanter.com/excel-programming/402316-flash-cell-colours-white-red-back-again.html)

MarkyB
flash cell colours from white to red and back again
 
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
flash cell colours from white to red and back again
 
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" wrote:

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

MarkyB
flash cell colours from white to red and back again
 
Sorry Joel nothing happens apart from showing the msgbox, but it has
eliminated the run-time error..

"Joel" wrote:

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" wrote:

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
flash cell colours from white to red and back again
 
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" wrote:

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

"Joel" wrote:

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" wrote:

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

MarkyB
flash cell colours from white to red and back again
 
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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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
flash cell colours from white to red and back again
 
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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

MarkyB
flash cell colours from white to red and back again
 
Nope, still no change. Index is indeed -4142. Only thing to happen is the
msgbox.
"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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
flash cell colours from white to red and back again
 
Isn't the cell red when the message box appears? Then turns back to blank
after message box closes

"MarkyB" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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

MarkyB
flash cell colours from white to red and back again
 
Brilliant! Must have had another cell active that I couldn't see on screen.

Thanks a million Joel

"Joel" wrote:

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

"MarkyB" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

"Joel" wrote:

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" wrote:

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


All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com