|
Flashing Cells
Hi everybody, I have a code that makes a specific cell blinking .. it works very well, but with one cell only .. I tried to develop it to be active with more than one cell, but I could not. This is the code and I wish I could find the answer he Public Sub Blink() With Sheets(1).Range("A1").Interior If .ColorIndex = 2 Then ..ColorIndex = 3 Else ..ColorIndex = 2 End If End With appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub Thank you, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Jim,
this is untested but it would go something like this Public Sub Blink() Dim rngCell as range Dim rngRegion as range set rngRegion =Sheets(1).Range("A1:d10") for each rngCell in rngRegion .Interior.ColorIndex = iif(.Interior.ColorIndex = 2,3,2) Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub Robin Hammond www.enhanceddatasystems.com "Jim333" wrote in message ... Hi everybody, I have a code that makes a specific cell blinking .. it works very well, but with one cell only .. I tried to develop it to be active with more than one cell, but I could not. This is the code and I wish I could find the answer he Public Sub Blink() With Sheets(1).Range("A1").Interior If .ColorIndex = 2 Then ColorIndex = 3 Else ColorIndex = 2 End If End With appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub Thank you, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Hi Jim,
Inserting a clearly intended With...End With clause, Robin's code ran without problem for me: Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub --- Regards, Norman "Jim333" wrote in message ... Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Thankyou Norman.
My mistake Jim. Robin Hammond www.enhanceddatasystems.com "Norman Jones" wrote in message ... Hi Jim, Inserting a clearly intended With...End With clause, Robin's code ran without problem for me: Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub --- Regards, Norman "Jim333" wrote in message ... Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Hi Norman, And the code to turn this off would be ?? Dave Don't say the trash can. :) Norman Jones Wrote: Hi Jim, Inserting a clearly intended With...End With clause, Robin's code ran without problem for me: Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub --- Regards, Norman "Jim333" wrot in message ... Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread http://www.excelforum.com/showthread...hreadid=401858 -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043 View this thread: http://www.excelforum.com/showthread.php?threadid=40185 |
Flashing Cells
Hi Dave,
Perhaps, by using a module level boolean (blStop) in conjunction with a macro (StartIt) to inititiate the blink routine and a macro (StopIt) to terminate the blink routine '====================== Option Explicit Public blStop As Boolean '------------------------------ Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double If blStop Then Exit Sub Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub '------------------------------ Public Sub StartIt() blStop = False Blink End Sub '------------------------------ Public Sub StopIt() blStop = True End Sub: '<<====================== BTW, Dave, I sincerely hope that you have not been converted to flash / blink phenomena - I thought *you* had more sense! --- Regards, Norman "Piranha" wrote in message ... Hi Norman, And the code to turn this off would be ?? Dave Don't say the trash can. :) Norman Jones Wrote: Hi Jim, Inserting a clearly intended With...End With clause, Robin's code ran without problem for me: Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub --- Regards, Norman "Jim333" wrote in message ... Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Was just working on a similar answer. Only difference to Norman is that this
allows you to store the range and it sets the cell colours back to white. Private appTime As Double Private rngSaved As Range Private bStop As Boolean Sub Test() InitBlinking ActiveSheet.Range("a1:d5") End Sub Sub InitBlinking(rngBlink As Range) Set rngSaved = rngBlink bStop = False Blink End Sub Sub StopBlinking() bStop = True 'if you were running longer delays you would also want code like this 'application.OnTime apptime,"Blink",,false 'For Each rngCell In rngSaved 'rngCell.Interior.ColorIndex = 2 'Next rngCell End Sub Public Sub Blink() Dim rngCell As Range For Each rngCell In rngSaved With rngCell If bStop Then .Interior.ColorIndex = 2 Else .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End If End With Next rngCell If Not bStop Then appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End If End Sub Robin Hammond www.enhanceddatasystems.com "Norman Jones" wrote in message ... Hi Dave, Perhaps, by using a module level boolean (blStop) in conjunction with a macro (StartIt) to inititiate the blink routine and a macro (StopIt) to terminate the blink routine '====================== Option Explicit Public blStop As Boolean '------------------------------ Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double If blStop Then Exit Sub Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub '------------------------------ Public Sub StartIt() blStop = False Blink End Sub '------------------------------ Public Sub StopIt() blStop = True End Sub: '<<====================== BTW, Dave, I sincerely hope that you have not been converted to flash / blink phenomena - I thought *you* had more sense! --- Regards, Norman "Piranha" wrote in message ... Hi Norman, And the code to turn this off would be ?? Dave Don't say the trash can. :) Norman Jones Wrote: Hi Jim, Inserting a clearly intended With...End With clause, Robin's code ran without problem for me: Public Sub Blink() Dim rngCell As Range Dim rngRegion As Range Dim appTime As Double Set rngRegion = Sheets(1).Range("A1:d10") For Each rngCell In rngRegion With rngCell .Interior.ColorIndex = IIf(.Interior.ColorIndex = 2, 3, 2) End With Next rngCell appTime = Now() + TimeValue("00:00:01") Application.OnTime appTime, "Blink" End Sub --- Regards, Norman "Jim333" wrote in message ... Thank you for ur reply, the code didn't work with me,, Can u please test it and attach a file contains the tested code, thanks, -- Jim333 ------------------------------------------------------------------------ Jim333's Profile: http://www.excelforum.com/member.php...fo&userid=5186 View this thread: http://www.excelforum.com/showthread...hreadid=401858 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=401858 |
Flashing Cells
Hi Norman, he he he, Well im kinda old for the flashing but i am after th knowledge. I want to fill the unused space in my brain. (don't want an blank cells) :) Your code works great, as expected. Hi Robin, Your code works great as well. thx to both of you Dave Norman Jones Wrote: Hi Dave, Perhaps, by using a module level boolean (blStop) in conjunction wit a macro (StartIt) to inititiate the blink routine and a macro (StopIt to terminate the blink routine BTW, Dave, I sincerely hope that you have not been converted to flas / blink phenomena - I thought *you* had more sense! Regards,QUOTE] Robin Hammond Wrote: Was just working on a similar answer. Only difference to Norman is tha this allows you to store the range and it sets the cell colours back t white.QUOTE -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043 View this thread: http://www.excelforum.com/showthread.php?threadid=40185 |
Flashing Cells
Hi everybody, Thanks you for everyone participated in this topic, especially Robi and Norman Thank you sooooooooo much for the great help yo delivered -- Jim33 ----------------------------------------------------------------------- Jim333's Profile: http://www.excelforum.com/member.php...nfo&userid=518 View this thread: http://www.excelforum.com/showthread.php?threadid=40185 |
| All times are GMT +1. The time now is 10:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com