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
leo
hi sir your skills is superb, i am new and learning the vba sir, can you teach me if how can i make a specific cells to blink to become yellow if it reaches 20hrs and below then it will turn to blinking red if it reach 10hrs and below, thanks and more power