Cell flash when not equal to current date

[Krishnab4u]

Dear Sir,
i take a bow for all your wonderful guidance for a newbee like me.
I am working in a excel file where I have to make a cell flash when date value of the same is not equal to current date and to stop when the value is equal to current date.

i used the following code.
But it doesnt stop after the current date is keyed in the cell.

this goes in the separate module

Option Explicit
Public RunWhen As Double
 
Sub StartBlink()
   If Worksheets("Summary").Range("X2:AA2").Interior.ColorIndex = 3 Then
      Worksheets("Summary").Range("X2:AA2").Interior.ColorIndex = 4
    Else
        Worksheets("Summary").Range("X2:AA2").Interior.ColorIndex = 3
    End If
    RunWhen = Now + TimeSerial(0, 0, 2)
    Application.OnTime RunWhen, "StartBlink", , True
End Sub
 
Sub StopBlink()
 Worksheets("Summary").Range("X2:AA2").Interior.ColorIndex = xlAutomatic
 Application.OnTime RunWhen, "StartBlink", , False
End Sub

the following goes inside the worksheet

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   If Range("Z2") <> Date And CellCheck = False Then
       Call StartBlink
       CellCheck = True
   ElseIf ("Z2") = Date And CellCheck = False Then
       Call StopBlink
        CellCheck = False
    End If
End Sub

Kindly help.

 

[shknbk2]

Typographical error, maybe? (Add "Range")

[COLOR=#333333]ElseIf Range("Z2") = Date And CellCheck = False Then[/COLOR]

 

[Krishnab4u]

Thank you.
I figured it out myself.
1. I added Worksheets("Summary").range
2. I corrected Range ("Z2").Value = Date
3. I have to unprotect the sheet using code.

 

[Krishnab4u]

I am facing a problem here.
Assuming that there are two sheets.
In Summary sheet i have Cell Flash code currently in execution. (cell is keep flashing)
Suppose i am updating some date in Sheet2 and attempting to close the workbook, i want the workbook to close only after i change the Flash cell value to current date.
For that i write below code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Check to see if Cell A1 is blank
    If Sheets("Summary").Range("Z2").Value <> Date Then


'Step 2: Blank: cancel the Close and tell the user
        Cancel = True
        MsgBox "Change Alloc. Week to Today's date" & vbOKOnly&
        Sheets("Summary").Range("Z2:AA2").Select
'Step 3: Not Blank; Save and Close
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

But, below line

        Sheets("Summary").Range("Z2:AA2").Select

is not executed and shows error as "Cant execute code in break mode".

Please help.

 

[shknbk2]

I'm not able to re-create the error when I put in the code above. However, just to make sure the code is correct, change this line to remove the "&". The syntax calls for a comma to separate the text from the buttontype.

MsgBox "Change Alloc. Week to Today's date", vbOKOnly

After this change, does it work correctly?

 

[shknbk2]

I had your example still running in Excel and just came back to it. I didn't have the code exactly right because I was also testing something else and had a line in the code of the Stop function that wasn't correct (it said Workbook.Open and nothing else). The code highlighted that iine as it should when running a procedure when the syntax is wrong and stopped to wait for me to fix it. Then, I'm assuming that the 2 second timer had expired and the Blinking function wanted to do its thing to change the color. However, since the code was stopped for the other error, the timed code couldn't run. Therefore, the message error about not being able to run the code (i.e., the StartBlink code) at the timer expiration.

I wonder if your code also stops somehow and then prevents the StartBlink code from running. Does everything compile when you hit Debug->Compile VBA Project?