Conditional Formatting is a powerful tool. In this example, I’ve populated the range A1:A20 with some numbers. Now I want a visual cue of which numbers are at each end of the spectrum. For my purposes, I want to know which numbers are within 10% of the largest number and which numbers are more than 90% away from the largest number.
First, select the range A1:A20 (ensuring that A1 is the active cell) and choose Conditional Formatting from the Format Menu. The completed Conditional Formatting box looks like this
A couple of items to note: The first formula compares A1 to 90% of the maximum value in the range A1:A20. If it’s greater than that number, the font color is changed to blue. The second formula compares A1 to 10% of the maximum value and colors the font red if it’s lower. The A1 in both formulas is relative (no dollar signs) so that as the formatting is applied down the range A1:A20, it uses the current cell.
Here’s what the final formatted range might look like:
Now for the real power! When the numbers change, the formatting adjusts. Since I used RANDBETWEEN to generate the numbers, a simple recalc (F9) changes the numbers to look like this:
You can use any formula that your heart desires. The formulas I used may not be useful to anyone but me, so find the formula that gives you the best information and use it.
Knowledge may be power, but Conditional Formatting is absolute power. Use it wisely, grasshopper.
Can you could use Conditional Formating to make the cells blink, just like a ‘ low fuel ‘ light on a cars dash board ?
I would imagine that you can write code to change the font colour to the backgroup and then back to red, and then run a loop to make it happen ever second or so. But, how would you keep the macro running even when you are navigating around the sheet ?
Master, I’m sure there are acient ways to make this work ?
Jake Nash
Jake… the easy answer is “Why in God’s name would you want to make your text blink?
The other answer is that you can’t do it with conditional formatting.
You could do it, I think, with the Application.OnTime command, but it would eat up a lot of processor time.
Here is a way to do it – Can’t recall where I got it. I found it irritating!
DIM NextTime as date
Sub Flash()
‘ If you define a new Style (Format / Style / Flash/ Add )
NextTime = Now + TimeValue(“00:00:01?)
With ActiveWorkbook.Styles(“Flash”).Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, “Flash”
End Sub
Thanks Mike, I would imagine it could become very irritating, much like Andyís comments !
Thanks
Do you know if there is any way to set more than 3 conditions? I’m using Excel 2000.
Is there a way to use Conditional Formatting
if there are empty cells? There are 3 values in one Column ex: below.
a1-blank
a2-blank
a3-black
a4-Value Needed “2?
a1-blank
a2-blank
a3-black
a4-Value Needed “3?
a1-blank
a2-blank
a3-black
a4-Value Needed “4?
thanksMarino
For a way to introduce all sorts of formatting effects including blinking cells or blinking data points in a chart — all without knowing VBA (except for a short routine that is provided to you) — see http://www.tmehta.com/tmxl/dbfind_byid.asp?id=14
Good Afternoon.
i want a formula to blinking my cell. not only cell but also whole words of cell and it blinking like star.
suppose there is one column which name is Result. velue of those cell are “Pass” or “Fail”, and i want a formula to blink which’s velue is “Fail”. thats its.
i waiting for your positive reply in my mail
Prakash Makwana