Solved: If....Flash cell-Module

[Mr.G]

Hi all.
Could someone help with this . I'm trying to write a module that can be called when these values are true. Two cells to the left will b e coloured black with a value in the cell.So when these or one of the values are true the cell to the left must flash the amount or fill colour so that you can see the value.Then when that amount gets rectified the flashing should stop.


[VBA] Private Sub Worksheet_Activate()
Range("A1").Select
If Sheet1.Range("O86") <= 30 Then GoTo Msg
'40x20x40 T
If Sheet1.Range("O87") <= 10 Then GoTo Msg
'40x25x40 T
If Sheet1.Range("O88") <= 10 Then GoTo Msg
'40x32x40 T
If Sheet1.Range("O89") <= 10 Then GoTo Msg
'20x20x16 T
If Sheet1.Range("O90") <= 80 Then GoTo Msg
'25x25x20 T
If Sheet1.Range("O91") <= 50 Then GoTo Msg
'25x16x20 T
If Sheet1.Range("O92") <= 50 Then GoTo Msg
End
Msg:
'Call BLINK
Notify.Show
End
End Sub

[/VBA]

Any help will be appreciated.
Thank you

[geekgirlau]

What about this: paste this code into a normal module
[vba]
Sub Blink()
Dim varRange(0 To 1, 0 To 6) As Variant
Dim i As Integer


varRange(0, 0) = "O86"
varRange(1, 0) = Sheet1.Range("O86") <= 30
'40x20x40 T
varRange(0, 1) = "O87"
varRange(1, 1) = Sheet1.Range("O87") <= 10
'40x25x40 T
varRange(0, 2) = "O88"
varRange(1, 2) = Sheet1.Range("O88") <= 10
'40x32x40 T
varRange(0, 3) = "O89"
varRange(1, 3) = Sheet1.Range("O89") <= 10
'20x20x16 T
varRange(0, 4) = "O90"
varRange(1, 4) = Sheet1.Range("O90") <= 80
'25x25x20 T
varRange(0, 5) = "O91"
varRange(1, 5) = Sheet1.Range("O91") <= 50
'25x16x20 T
varRange(0, 6) = "O92"
varRange(1, 6) = Sheet1.Range("O92") <= 50

For i = 0 To UBound(varRange, 2)
With Sheet1.Range(varRange(0, i))
If varRange(1, i) = True Then
If .Font.ColorIndex = 1 Or .Font.ColorIndex = 3 Then
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
Else
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 3
End If
Else
' clear the cell/font colours
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End If
End With
Next i
Application.OnTime Now() + TimeValue("00:00:02"), "Blink", , True
End Sub
[/vba]

Then in ThisWorkbook you need the following:
[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Now(), "Blink", , False
End Sub
[/vba]
[vba]
Private Sub Workbook_Open()
Application.OnTime Now(), "Blink", , True
End Sub

[/VBA]

[Mr.G]

Thank you that would work if I only had those cells that I'm watching but there is at least 200 cells that is of different values that is in the Sub. That is why I would like it to call the blink module at the end of the Sub.

[Bob Phillips]

I think this is too difficult.

I c ame up with a way to monitor multiple cells and set invidual timers, but it was hell trying to get them to stop.

[Mr.G]

That would be shame......that would have helped a lot. Do you have any ideas as how I can do something similar.....like if that value is true then the other cell(two cells to the left of the <= value) will have a fill colour of white and if false a fill colour of black?
Still need to call from a module.

[Bob Phillips]

That should be easy

[vba]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O86:O92" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Address(False, False)
Case "O86"
If .Value <= 30 Then
.Offset(0, -2).Interior.ColorIndex = xlColorIndexNone
.Offset(0, -2).Font.ColorIndex = xlColorIndexAutomatic
Else
.Offset(0, -2).Interior.Color = vbBlack
.Offset(0, -2).Font.Color = vbWhite
End If
Case "O87", "O88", "O89"
If .Value <= 10 Then
.Offset(0, -2).Interior.ColorIndex = xlColorIndexNone
.Offset(0, -2).Font.ColorIndex = xlColorIndexAutomatic
Else
.Offset(0, -2).Interior.Color = vbBlack
.Offset(0, -2).Font.Color = vbWhite
End If
Case "O90"
If .Value <= 80 Then
.Offset(0, -2).Interior.ColorIndex = xlColorIndexNone
.Offset(0, -2).Font.ColorIndex = xlColorIndexAutomatic
Else
.Offset(0, -2).Interior.Color = vbBlack
.Offset(0, -2).Font.Color = vbWhite
End If
Case "O91", "O92"
If .Value <= 50 Then
.Offset(0, -2).Interior.ColorIndex = xlColorIndexNone
.Offset(0, -2).Font.ColorIndex = xlColorIndexAutomatic
Else
.Offset(0, -2).Interior.Color = vbBlack
.Offset(0, -2).Font.Color = vbWhite
End If
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

[Mr.G]

Thanx xld that would work once again if it was only those cells but there is at least 200 different cells in different rows in that code.
That's why I want to call it from a standard code module.

Any ideas?

[Bob Phillips]

That can be extended to 200 cells. The problem is that you have variable amounts to test depending on the cell.

[Mr.G]

So it would just beter to abandon the idea instead of trying to carry on with this?

[mikerickson]

It sound like Conditional Formatting would do what you want without flashing.

[Bob Phillips]

It sound like Conditional Formatting would do what you want without flashing.

I think the number of variations preclude CF.

[lucas]

This might be hard to configure but it works on more than 3 conditions

Conditional Formatting (More Than Three)

[Mr.G]

Yeah I've got conditional formatting on that already with 3 different colours for three different stages of the cycle.
But in the middle of the "between" conditional format I would like it to decolour that block. Then the program sends out a mail to warn you that the level is low and you should check the sheet to adjust the needed.
If there is at least 50 "between's" on and you need to go through all of them to find one..................ouch!
I don't realy have a problem with this but I know what to look for.I just cannot say the same for the poor person that is 1000 odd miles away trying to figure out witch cell needs the adjustment.