Skip to content

Apply Blinking Conditional Formatting in Excel

    This video tutorial demonstrates how you can apply blinking conditional formatting to a range of cells in Excel.  The solution requires you to use a VBA macro as there is no inbuilt functionality that will achieve this – please see the code below.

    In the code provided you can specify how many times the cells blink by changing the number of For Next loops that are performed, by default it is two.  Why only two?  Whilst the blinking effect is applied the worksheet is unusable as the code is busy looping in the background.  I would think carefully about using this effect in a worksheet for this reason.

    Please download the featured file here.  Note the file does not contain the VBA code as I am unable to upload Macro Enabled files to this website.

    Here’s the code featured in the video. Please feel free to copy it into your own project.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim SalesValue As Range
    
    Dim SalesField As Range
    Set SalesField = Range("B2", Range("B2").End(xlDown))
    
    Dim SalesTarget As Range
    Set SalesTarget = Range("E1")
    
    Dim CellsToBlink As Range
    Dim I As Byte
    
    'Only run the code below if either the sales or sales target values are changed
    If Not Intersect(Target, Union(SalesField, SalesTarget)) Is Nothing Then
    
    SalesField.Interior.Color = vbWhite 'Reset all cell background colours in column B to white
    
    For Each SalesValue In SalesField 'For each sales figure in the sales field
        
        If SalesValue >= SalesTarget Then 'If the sales value has met the target
            
            If CellsToBlink Is Nothing Then ' and if the CellsToBlink variable is empty
                ' store the current SalesValue in the CellsToBlink Variable
                Set CellsToBlink = SalesValue
            Else
                'if the CellsToBlink variable is not empty,
                'add the new SalesValue to the CellsToBlink variable
                Set CellsToBlink = Union(SalesValue, CellsToBlink)
            End If
        
        End If
           
    Next SalesValue
    
    'Now all the eligible cells have been added to the CellsToBlink variable
    'Change the cells's background colour to yellow then white with a 1 second interval
    For I = 1 To 2 'do this twice
        
        CellsToBlink.Interior.Color = vbYellow
        Application.Wait Now + TimeValue("00:00:01")
        CellsToBlink.Interior.Color = vbWhite
        Application.Wait Now + TimeValue("00:00:01")
    
    Next I
    
    
    'Once the blinking is over change any eligible cell's background colour to yellow
    CellsToBlink.Interior.Color = vbYellow
    
    End If
    
    End Sub