VBA Express : Excel - Make specific cell blink based on a condition

Excel

Make specific cell blink based on a condition

Ease of Use

Intermediate

Version tested with

2003

Submitted by:

malik641

Description:

Makes a cell blink Red and Yellow if an invalid value is entered in that cell. Then stops blinking when that cell has a valid value in it.

Discussion:

If a user enters an invalid value in a cell and you want than just condional formatting. You can make the cell blink untill the value is changed to a valid value.

Code:

instructions for use

			 'Place this in a Standard Module
 
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

How to use:

Press Alt+F11 to open the VBE
Go to Insert --> Module
Place the first section of the code into the new module
Go back to the excel Workbook and right-click the Worksheet you want to have the blinking cell in and click on "View Code"
Place the second part of the code in this module

Test the code:

Go to the worksheet that you placed the Worksheet event in and place a 1 in cell A1 to activate the blinking
Then place anything else in cell A1 to deactivate the blinking cell

Sample File:

Cell Blink.zip 8.78KB

Approved by mdmackillop

This entry has been viewed 449 times.