Conditional Formatting Corrupts Absolutely

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.

8 thoughts on “Conditional Formatting Corrupts Absolutely

  1. 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

  2. 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.

  3. 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

  4. Thanks Mike, I would imagine it could become very irritating, much like Andyís comments !

  5. Is there a way to use Conditional Formatting
    if there are empty cells? There are 3 values in one Column ex: below.

    a4-Value Needed “2?
    a4-Value Needed “3?
    a4-Value Needed “4?


  6. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.