PDA

View Full Version : Help with "StartBlink"



prepalot
01-26-2008, 11:38 AM
Hoping someone can please help me with the macro which I?ve posted below (not mine, got it from this site). I?m trying to tailor it to my needs. BTW, I am new to this site ? and new to VBA altogether.
Example of what I?m trying to do: I want to apply the blinking cell property to (let?s say) A1, but only when K11 meets a certain condition. Additionally, in the same worksheet, I would like A2 to blink, but only when K12 meets a certain condition. In other words, I would like these specifically married cells (A1 and K11) or (A2 and K12) to work together in activating this code ? simultaneously ? in the same worksheet.
Can this even be done? I?ve tinkered and tinkered with no success.
If you have a better code to accomplish this ? please feel free to post it.
Thank you in advance - your brilliant expertise would be respectfully appreciated!


Option Explicit
Public RunWhen As Double

Sub StartBlink()
If Range("A1").Interior.ColorIndex = 3 Then
Range("A1").Interior.ColorIndex = 6
Else
Range("A1").Interior.ColorIndex = 3
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub

Sub StopBlink()
Range("A1").Interior.ColorIndex = xlAutomatic
Application.OnTime RunWhen, "StartBlink", , False
End Sub




'Then Place this code into the code module of the specific worksheet you want to work with

Option Explicit
Public CellCheck As Boolean

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Change the IF statement and cell range to what you need
If Range("A1") = "1" And CellCheck = False Then
Call StartBlink
CellCheck = True
ElseIf Range("A1") <> "1" And CellCheck = True Then
Call StopBlink
CellCheck = False
End If

End Sub

Simon Lloyd
01-27-2008, 01:04 AM
This works, put this in a standard module:

Option Explicit
Public RunWhen As Double
Sub StartBlink()
If Range("K11") = 1 Then
If Range("A1").Interior.ColorIndex = 3 Then
Range("A1").Interior.ColorIndex = 6
Else
Range("A1").Interior.ColorIndex = 3
End If
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub
Sub startblink1()
If Range("K12") = 1 Then
If Range("A2").Interior.ColorIndex = 3 Then
Range("A2").Interior.ColorIndex = 6
Else
Range("A2").Interior.ColorIndex = 3
End If
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink1", , True
End Sub
Sub StopBlink()
Range("A1").Interior.ColorIndex = xlAutomatic
Range("A2").Interior.ColorIndex = xlAutomatic
Application.OnTime RunWhen, "StartBlink", , False
End Sub
Sub StopBlink1()
Range("A2").Interior.ColorIndex = xlAutomatic
Application.OnTime RunWhen, "StartBlink1", , False
End Sub
put this in the worksheet module:

Option Explicit
Public CellCheck As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Change the IF statement and cell range to what you need
If Range("K11") = "1" And CellCheck = False Then
Call StartBlink
CellCheck = True
ElseIf Range("K11") <> "1" And CellCheck = True Then
Call StopBlink
CellCheck = False
End If
If Range("K12") = "1" And CellCheck = False Then
Call startblink1
CellCheck = True
ElseIf Range("K12") <> "1" And CellCheck = True Then
Call StopBlink1
CellCheck = False
End If
End Sub

prepalot
01-27-2008, 08:57 AM
Going to try this - Thanks so much for the time and effort Simon! Will get back with the results.
-Prepalot

prepalot
01-27-2008, 09:33 AM
Simon,
When I close out the VBA editor, and run the numbers in the corresponding fields, I get runtime a "Runtime Error 1004". Otherwise, when I run throught each individual script using the "Run Sub/UserForm" window, things run smoothly.
Any idea of what I may be doing wrong?
BTW.. I'm using Excel 2003 if this helps.
Thanks!!
Prepalot

prepalot
01-28-2008, 09:33 AM
First, Thanks to Simon Lloyd for getting me halfway there...
Here's the rest - which I was able to finally figure out. So far it works exactly the way I want it to:

PASTE BELOW TO SHEET:

Option Explicit
Public CellCheck As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("K11") = "1" And CellCheck = False Then
Call StartBlink
CellCheck = True
ElseIf Range("K11") <> "1" And CellCheck = True Then
Call StopBlink
CellCheck = False
End If
If Range("K12") = "1" And CellCheck = False Then
Call startblink1
CellCheck = True
ElseIf Range("K12") <> "1" And CellCheck = True Then
Call StopBlink1
CellCheck = False
End If
End Sub

PASTE BELOW TO MODULE:

Option Explicit
Public RunWhen As Double
Sub StartBlink()
If Range("K11") = 1 Then
If Range("A1").Font.ColorIndex = 1 Then
Range("A1").Font.ColorIndex = 3
Else
Range("A1").Font.ColorIndex = 1
End If
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub
Sub startblink1()
If Range("K12") = 1 Then
If Range("A2").Font.ColorIndex = 1 Then
Range("A2").Font.ColorIndex = 3
Else
Range("A2").Font.ColorIndex = 1
End If
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink1", , True
End Sub
Sub StopBlink()
Range("A1").Font.ColorIndex = 1
End Sub
Sub StopBlink1()
Range("A2").Font.ColorIndex = 1
End Sub

Thanks again!!

Prepalot