Blinking Conditional Formatting... Yes Excel Is Blinking

Summary

In this tutorial, Nabil Murad demonstrates how to apply a blinking conditional formatting effect in Excel. By using VBA, users can make a cell blink when certain conditions are met, such as when the total revenue falls below the sales goal. The tutorial covers creating a module in Visual Basic, writing the code for the blinking effect, and setting up worksheet events for automatic triggers when sales data is updated. Additionally, it shows how to stop the blinking by entering a value in a specific cell. This practical guide helps enhance Excel's visual appeal and functionality.

Takeaways

• 😀 Conditional formatting in Excel allows users to change font and cell colors when specific conditions are met.
• 😀 In this tutorial, Nabil Murad demonstrates how to create a blinking effect in Excel cells using conditional formatting.
• 😀 The goal is to highlight cell H1 by making it blink when company revenue is below the sales goal.
• 😀 The code is written in Visual Basic (VBA) to achieve the blinking effect based on certain conditions.
• 😀 The blinking effect is triggered when the total company revenue (cell F20) is less than the sales goal (cell B1).
• 😀 The VBA code uses a 'Do While' loop to continuously alternate the fill color of H1 between orange and white.
• 😀 A brief pause (1 second) is included between color changes to create the blinking illusion.
• 😀 The blinking stops when the total revenue exceeds the sales goal, which is monitored in real-time.
• 😀 A worksheet event is set up to automatically trigger the blinking effect when any sales data (from B5 to E19) is changed.
• 😀 To stop the blinking effect manually, typing '1' in cell D1 halts the macro and exits the loop.

Q & A

What is the purpose of the blinking conditional formatting in this Excel tutorial?

The purpose of the blinking conditional formatting is to highlight a cell (H1) to grab attention when the total company revenue (cell F20) is below the sales goal (cell B1), signaling that action needs to be taken.

How does the blinking effect in the cell work?

The blinking effect is simulated by repeatedly changing the cell color between orange (color index 44) and white (color index 0) every second. This creates the illusion of blinking as long as the total revenue is below the target.

What key function in Excel is used to compare the total revenue with the sales goal?

The key function used is a conditional formula in cell H1, which compares the total revenue (cell F20) to the sales goal (cell B1). It returns 'Target Met' if the goal is met or 'Below Target' if the goal is not met.

What do you need to do to activate the blinking effect in Excel?

To activate the blinking effect, the user needs to change any sales data in columns B to E (B5 to E19). This triggers the worksheet event, which calls the blinking subroutine if the total revenue is below the sales goal.

How is the blinking effect stopped in this Excel setup?

The blinking effect can be stopped by typing '1' in cell D1. This triggers the exit condition in the code, stopping the loop and resetting the blinking.

What is the role of the Visual Basic editor in this tutorial?

The Visual Basic editor is used to write the VBA code that creates the blinking effect and triggers the conditional formatting. It allows the user to automate the process of changing cell colors based on conditions like meeting or missing sales goals.

What type of event triggers the blinking conditional formatting to activate?

The blinking conditional formatting is activated by a worksheet change event. Whenever a change is made to any of the sales values in cells B5 to E19, the macro is called to check if the total revenue is below the sales goal.

What does the 'Do While' loop do in the VBA code?

The 'Do While' loop repeatedly checks if the total company revenue (cell F20) is less than the sales goal (cell B1). If true, it changes the fill color of cell H1 to simulate blinking, and the loop continues until the revenue is equal to or greater than the target.

Why is there a delay of one second between changing the cell colors in the code?

The one-second delay (using 'Application.Wait') between changing the cell colors is necessary to make the blinking effect visible and allow the user to perceive the changes. This creates a smooth transition between colors.

What happens if the total revenue in cell F20 is greater than or equal to the sales goal?

If the total revenue in cell F20 is greater than or equal to the sales goal in cell B1, the blinking effect stops and the color of cell H1 changes back to white, indicating that the target has been met.