S
sonicscooter
Hi, i wanted to have several cells were if the value reaches a certain amout
a third cell will slowly flash, the code below works to some extent but not
properly, cell A1 flashes blue when H1 is higher in value than G1, but i
wanted cell B1 to flash if I1 was greater than J1 and so on, but it doesn't
work correctly, the top half of the code takes presidence over the later half.
Any ideas were i have gone wrong?
Mnay thanks for any help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H1") > ("G1") Then
Beep
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed
Set myCell = Range("A1")
newColor = 41
fSpeed = 0.5
Do Until x = 10
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Else
If Range("I1") > Range("J1") Then
Beep
Set myCell = Range("B1")
newColor = 24
fSpeed = 0.5
Do Until x = 10
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Else
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
Application.EnableEvents = True
End If
End If
End Sub
a third cell will slowly flash, the code below works to some extent but not
properly, cell A1 flashes blue when H1 is higher in value than G1, but i
wanted cell B1 to flash if I1 was greater than J1 and so on, but it doesn't
work correctly, the top half of the code takes presidence over the later half.
Any ideas were i have gone wrong?
Mnay thanks for any help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H1") > ("G1") Then
Beep
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed
Set myCell = Range("A1")
newColor = 41
fSpeed = 0.5
Do Until x = 10
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Else
If Range("I1") > Range("J1") Then
Beep
Set myCell = Range("B1")
newColor = 24
fSpeed = 0.5
Do Until x = 10
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
Else
Application.StatusBar = False
Application.DisplayStatusBar = Application.DisplayStatusBar
Application.EnableEvents = True
End If
End If
End Sub