Complex problem involving VBA in excel

[FloggingDolphin]

Sorry if this might sound familiar to some of you but I hope someone ca
help me with some 'challenges'.

I’m trying to create a nice-looking interactive worksheet. I hav
obtained two vba programs from some free websites. This first one let
my cell blink white and purple. Originally it was to make many cells d
that and in multiple colours but I’ve edited it and I’m not sure i
there is any unnecessary lines of code in it. Also, I have a problem
when I quit it closes my whole excel application (including othe
worksheets). If I say cancel with regard to saving in other worksheets
for some reason my blinking work sheets re-opens itself (it will kee
doing so unless I disable macros).
Below is the code for the blinking cell (code for ThisWorkBook and cod
in a Module):


----------------ThisWorkBook----------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Quit
End Sub


Private Sub Workbook_Open()

Call Blink

End Sub



----------------module---------------


Dim BlinkTime As Date

Public Sub Blink()
Dim c As Range

For Each c In Range("C3").Cells
If c.Interior.ColorIndex = 47 Then
If c.Value > 100 Then
c.Interior.ColorIndex = 2 'White
ElseIf c.Value > 50 Then
c.Interior.ColorIndex = 2 'White
Else
c.Interior.ColorIndex = 2 'White
End If
Else
c.Interior.ColorIndex = 47
End If
Next c


BlinkTime = Now() + TimeValue("00:00:01")
Application.OnTime BlinkTime, "Blink"
End Sub


In addition, I have another vba program code that basically highlight
the row of the selected cell. It works really well because it restore
the any previous colours in cells after I’ve moved it elsewhere
However if I save the work sheet before quitting, it saves th
highlighted row. The code for this is listed below (I keep it in Shee
9 which is called REPORT).



---------------------Sheet9 (REPORT)----------------

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS As Long = 256
Const cnHIGHLIGHTCOLOR As Long = 38 'default lt. blue
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.ColorIndex
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
End Sub


What I’d like to do:

1. Get my worksheet to quit properly with regard to the blinking cell.
2. Make the row highlighting only applicable to rows 7 to 100 in m
worksheet.
3. Prevent my worksheet from saving the highlighted row after save.
4. Combine both the blinking cell and row highlighting into the sam
worksheet.


Is it correct to assume that the blinking cell and row highligh
programs cannot work if the worksheet is protected?

Thanks in advance to anyone who can figure all of the above out! (
know it’s quite a lot).

F

 

[Bob Phillips]

The first problem is because you have an outstanding Ontime call, which when
invoked will open the workbook with the scheduled macro.

Before quitting ensure that your code issues a

Application.OnTime BlinkTime, "Blink",,False

PS serves you right for having blinking cells <vbg>


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Devin Linnington]

For problem #2 just use an If statement along with a ActiveCell.Row
statement. Make it exit sub or something if ActiveCell.Row > 100 and < 7.