Conditional Formatting is widely used in Excel and it enables us to change Font characteristics and change cell color based on value when the desired conditions are met

In this tutorial I show you how I apply a Blinking Conditional Formatting to a cell that starts blinking if a condition is met and stops blinking when the condition is gone.

You can download the Exercise File for change cell color based on value

and follow along by clicking on the link here below

In This worksheet I have a list of employee names in column A and in columns B:E I have the quarterly sales of each employee.

In column F I have the total sales for each employee and in cell F20 I have the Total company sales.

The sales goal is in cell B1.

I created in cell H1 a conditional IF function that compares the Revenue in cell F20 to the sales Goal B1, and returns “Target Met” if we go above the target and “Below Target” if we do not meet the sales goal.

I want to create in Cell H1 a Blinking conditional formatting to let the cell stand out if we do not achieve the target, and stops blinking if the target is met.

To do that, I switch to the Visual Basic Editor by hitting ALT + F11

In the Visual Basic, I click on the Insert Menu and click on Module.

I start creating my module by typing Sub BlinkCell followed by hitting Enter.

I declare a Variable to store the Cell reference of the cell To Blink. The data type is a Range. I then assign a value to this variable by using the keyword Set.

To start Blinking, I use a Do Loop statement that keeps repeating instructions so long as the Total Revenue in F20 is Below the Target in Cell B1.

Between the Do… and Loop I write my instructions.

The concept is to Change Cell H1 to Orange (ColorIndex = 44) ► Then wait a Second ► Then Change it to White ► Wait a Second ► Then back to Orange.

We’ll keep looping over these instructions.

To be able to see the blinking in the worksheet and to be able to use the worksheet while the code is looping, I type “DoEvents”.

If the Revenue in F20 changes and becomes greater than the target B1 ► Then we change the Fill color of Cell H1 To White , which stops the Blinking.

To be able to stop the Blinking at any time, I add a conditional statement that Exits the Do if we type 31 in Cell D1.

You can copy and paste the code in your VBE

Sub BlinkCell()

Dim CellToBlink As Range

Set CellToBlink = Range(“H1”)

Do While Range(“F20”).Value < Range(“B1”).Value

CellToBlink.Interior.ColorIndex = 44

Application.Wait (Now + TimeValue(“0:00:01”))

CellToBlink.Interior.ColorIndex = 0

Application.Wait (Now + TimeValue(“0:00:01”))

CellToBlink.Interior.ColorIndex = 44

DoEvents

If Range(“D1”).Value = 1 Then Exit Do

Loop

If Range(“F20”).Value >= Range(“B1”).Value Then

CellToBlink.Interior.Color = vbWhite

End If

End Sub

To trigger this code without going to the visual Basic Editor, I add a Worksheet Change Event. How to do that?

Right-click on the sheet tab in Excel ► Click on View Code.

We are back to the visual basic editor and we have two drop lists at the top:

From the Top Left drop list ► Select Worksheet

An unnecessary Private Sub is created ► Delete it after adding the correct one.

From the Top Right drop list select ►Change ► Creates a Change Event ► Between the Private Sub and End Sub write the code.

There is another blog from our good friends at Ablebits to learn more about change cell color based on value.

You can read more about changing cell color based on value here

You can copy and paste the code here below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range(“B5:E19”)) Is Nothing Then

Range(“D1”).ClearContents

Call BlinkCell

End If

End Sub

This code means:

If there is any change in values in the quarterly sales of any employee (Range B5:E19) then, do the following:

• Clear the contents of Cell D1
• Call the Subroutine “BlinkCell”

Test your code as follows:

Change values in the Range B5:E19 so that the Revenue in F20 is Below the Target in B1 ► H1 keeps Blinking

Change values in the Range B5:E19 so that the Revenue in F20 is Above the Target in B1 ► H1 Stops Blinking

Change values in the Range B5:E19 so that the Revenue in F20 is Below the Target in B1 ► H1 keeps Blinking ► Type #1 in cell D1 To stop the Blinking of H1.

You can Watch the Video Tutorial here below