flash day

[still learning]

Hi

I have dates ( 10/29/20 formatted as mmm-dd) in range “days”, which is column A
When I open the workbook, I have a open macro (in this workbook) that go's to today’s date and scroll it to the top .I only use sheet 1

Private Sub Workbook_open()
Set C = Range("A:A").Find(Date)
If Not C Is Nothing Then C.Select
ActiveWindow.ScrollRow = ActiveCell.Row
ActiveWindow.SmallScroll Down:=-1
End Sub

After the open macro runs, I’m trying to get the cell to flash
The below stops at >>>If Range(“days”)….>
I have to get that corrected to see if anything else is wrong with it
this macro is in sheet 1

Sub flashday()
Dim i As Long
If Range("days").Value = [Today()] Then
Application.EnableEvents = False
With Range("days")
For i = 1 To 1
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i
End With
Application.EnableEvents = True
End If
End Sub

mike

 

[offthelip]

try this:

Sub flashday()
Dim i As Long
Set c = Range("A:A").Find(Date)
If c.Value = [Today()] Then
Application.EnableEvents = False
With c
.Font.ColorIndex = 4
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
End With
Application.EnableEvents = True
End If
End Sub

Note I changed the color index to 4 because 2 doesn't show up on my screen

 

[still learning]

Hi offthelip
happy halloween
works great, thanks
But I forgot i have the cell formatted to change from black to RGB 192,0,0 when the cell is the current day
It'll be easy to make a macro to delete formatting and then after the flash to bring the formatting back, but would there be a better way?

Mike

 

[Michael M]

Maybe loop the flashing, then reformat the call after

If c.Value = [Today()] Then
Application.EnableEvents = False
For n = 1 To 5
With c
.Font.ColorIndex = 4 'make the colour loop as many times as you want
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
End With
n = n + 1
Next n
'Add you reformatting code here
Application.EnableEvents = True
End If

 

[still learning]

Hi Mike
happy halloween
I added
Selection.FormatConditions.Delete
after the if line
and made a macro to copy the formatting of the cell above after next n

It works perfect
thank you

and thank you again offthelip

mike

 

[Michael M]

Glad WE could help.. ?

 

[still learning]

A related question
why ??
Application.EnableEvents = False
and
Application.EnableEvents = False

miked

 

[Michael M]

If you were using the code in a worksheet change event you would need those lines.
Otherwise I believe they are redundant.

 

[offthelip]

Since the orginal post implied the code was run from the workbook open event. I put the Application.EnableEvents = False : Application.EnableEvents = True in the code. This is because if at a later date somebody put some code in the worksheet change event code, this workbook open could cause the worksheet change event to trigger multiple times which slows down the workbook open event and might possibly cause a problem if the worksheet change event code is not well written. It is often very difficult to spot problems where one event triggers another event. So I think it is good practice to avoid triggering events when you don't need to. So not absolutely necessary, but not redundant, they are there for best practice reasons. I suggest leaving them. they have no disadvantages

 

[still learning]

Thank you, offthe lip, for that

getting close to hand out candy


mike