Free Microsoft Excel 2013 Quick Reference

Conditional formatting on cell value 0 Results

Hi all.

My problem is i have a range B1:B10 with conditional formating, depending on the value of A1 cell. IF A1 = 1 then cells color in range will be draw in white; if A1 = 0 then range will be formated to black color.

But i also want that the cells be deactivated for the people don't can write nothing when the conditional formating is black; and the cells deactivated when the cells is white.

I hope someone can help me, I appreciate to much.



Something that has always bugged me in Excel (2003) is the way that conditional formatting works for blank cells.

I have tracking sheets where I want to shade values of a 0 as orange, below 0 as red, and greater than 0 as green.

Thing is, I want cells with no value in to be unshaded. But the conditional formating treats the blank cells as GREATER THAN 0. Why??? Why is a null greater than 0?? Also if I replace the null with a space it still treats a space greater than 0!!

I don't want the blanks shaded green, as I want people to scan down the list and easily pick out those above/below. and there are too many randomly placed blanks (in multiple columns) to remove conditional formatting from the blanks.

I can usually find a way around it by using a "between 0 and 100000" or something like that, but it usually take some fiddling to get it to work and it just seems stupid to me that it treats a null and a space as greater than 0.

Is there a reason for this??

[Excel 2003] I have a complicated formula which returns either a +/-number, or one of several text messages in a cell. I am trying to add conditional formatting to the cell. If the cell returns a number, I want to add three conditions so that the cell is red, yellow, or green based on the amount of time before/after the due date of a project. If the cell returns a text message (e.g., "Closed"), I want the cell to default to white (the normal background color). I thought I could do this since I understand conditional formatting allows you three colors, plus the default when a condition is not met (thus four). I had hoped that "text" would not meet the conditions I set which are based on numbers. The conditional formatting seems to be recognizing the text as a number though, because when the cell formula returns text, the background is red (same condition as items >0 past due date.

My goal is that:
if projects are "Closed" (non-numerical text should default to white);
if project is overdue (numerical cell value >0), red;
if project is due within 30 days (cell value between 0 and -30), yellow;
if project is not due for 31+ days, green.

The numbers are based off a formula which looks at the due date versus today() to calculate a numeric value for # days overdue.
My conditions are:

1. Cell value is greater than 0 (red)
2. Cell value is between 0 and -30 (yellow)
3. Cell value is less than or equal to -31 (green)

Any suggestions would be greatly appreciated. I do not know how to use VBA so I am trying to do this with formulas and/or formatting.

Thank you.

D20 is formated to display yellow, if the cell says BETWEEN "30 - 11 DAYS REMAINING" and red, if D20 says less than or equal to "10 DAYS REMAINING". When I test to see if D20 changes color (I test it by changing the computer date), the cell does change to yellow, BUT, it does not change to red when the cell displays less than or equal to "10 DAYS REMAINING"

Condition 1 is formatted with "Cell Value is", Between, ="30 DAYS REMAINING", ="11 DAYS REMAINING"

Condition 2 is formatted with Cell Value is", less than or equal to, ="10 DAYS REMAINING".


Another cell ,D13, formated in a similar way, except, this particular cell is has a formula to count down the number of days base on another cell, B13.

Ex. If B13 says "1st class", than D13 will count down the number of days remaining. Once D13 reaches 0 days remaining, than B13 changes to say "2nd Class", and D13, will display the number of days remaining. Once D13 reaches 0 days remaining, than B13 changes to say "3rd Class", and D13, will display the number of days remaining.

When D13 displays between 30 - 11 DAYS REMAINING, D13 stays yellow no matter how many days are remaining.

In the conditional format,

Condition 1 is formatted with "Cell Value is", Between, ="30 DAYS REMAINING", ="11 DAYS REMAINING"

Condition 2 is formatted with Cell Value is", less than or equal to, ="10 DAYS REMAINING".

What seems to be the problem?

Is there a way using existing Excel functions to use conditional formatting on entries in (say) column D based on a value in column C? I have populated column C with two options (call them 0 and 1) and want to change the font for the entry for a value in (say) cell D12 to italics if the entry in cell C12 is "1".

Plan B is to use VBA on this.


I have conditional formatting set up for many cells based on a formula like
cell I93 conditional format is =$D$90

Hi - using Excel 2003:

I'm stumped on conditional formatting for a comparing 2 columns of
text values per row.

So ....

Current Compare Contract#
Name To

Smith Smith 123456
Jones Greenburg 111222
Johnson Johnson 222111

Column B, Row 2 should be Highlighted

Here's the conditional formatting I've applied:
FormulaIs = B1A1

& then paste the formula down column B

This has the effect of highlighting every cell, where it seems to
evaluate to true all the time. Try this with different variations of $
$$$ & it's about the same effect.

Two things to mention:

1.) I use this same method on a column with Number values & it works
2.) The names in column B are based on the following formula:


Where PI_Last is a named range of text (names) on another sheet in the
workbook and Contract is a named range of text values on the current
spreadsheet (Col C). I'm trying to compare where the names are
different for same contract between the two spreadsheets.

Thanks in advance for help.

Is there a way to have more than just the 3 conditions in formatting or to
work around it?

I have a range of b4:b35 and I need to highlight cell different colors
depending on cell value..
If cell =0 then fill with red
if cell =1 then fill with yellow
if cell = 2 then fill with green
if cell = 3 then fill with blue
if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts
out there have an alternative solution for me....

thank you in advance

I've read the posts on conditional formatting for cell colour based on
another cell's value (eg. set the conditional formatting to "formula
is" and then "=A1>0" and set the colour as red / blue / whatever...),
however wondering if I can do this for an entire column without
individually changing the conditional formatting for each cell one by
one (as there are over 400 rows).

Basically I need a formula that reads the contents of column B for the
particular row that is active.

Can anyone help?

Rob Moyle
Rob Moyle's Profile:
View this thread:

I apologize if this is a duplicate post, not sure what happened to the post I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the conditional
However, when I try to select all and apply the exact same formula (thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?


I would like to create a conditional format based on the content of the cell,
rather than the value.

I have a spreadsheet that has empty cells, cells with values of 1 and 2, and
cells that will have values of 0 based on 1-1 or 2-2. I do not want to
highlight the blank cells, I only want to highlight the cells that contain a
formula, even if the formula's value is 0. How can I do that? "Cell Value"
looks at the result of the formula and highlights all blank cells. I cannot
figure out what formula to use to look at the cell content rather than the
cell value.

For example:

Cell A1 is blank -- no highlight
Cell A2 value is 1 -- no highlight
Cell A3 value is 0 because formula is =1-1 -- highlight.

I only want to highlight the last cell because is contains a formula.

Thanks in advance!


I have hit the problem in Excel, that any function that references a blank
cell destroys the "blankness" of the result. You can convert Null (a blank
cell) to zero, or an empty string, but these are not the same as blank!

In the original source range, I can use a conditional format of "Cell value
is not 0" (bizarrely, but it works) to highlight cells that contain any
number or text. I can also create subtotal lines and use the SubTotal count
function SubTotal(3, [Range]) to count the nonblank cells.

However if I make a duplicate copy of the range on another sheet using any
function, the function result is always converted to zero (which screws up
the subtotal count) or an empty string (that screws up the conditional

I tried writing a custom "Keepblank()" function, but even here if the
function returns a null value Excel is converting this to a zero.

This has to be an FAQ. Is there a way with functions, or do I have to write
VBA code to bulk-copy the range to retain the blank values?

I have used conditional formatting extensively in the past, but I have
encountered a bizarre problem with applying it in this instance.

I want to compare 2 values in the same row (but different columns obviously)
to see if they are the same or different and change the background color of
the cell. I am using OFFSET and indirect functions to find the correct cells
to compare.

If I put the same formula in the conditional format statement and in the
cell itself, the conditional format is not changing the color when the
condition changes to true. The cell is calculating TRUE or false correctly
but no change in formats. (Yes, I have setup the change of formats in the
conditional format window)

Long term, I don't want to have the formula in the cell as well as in the
conditional format because I want the cell value to be able to be anything
while the format flags the changes for me.

I have tried switching from 1 worksheet and back. Recalcing, closing and
reopening and it still doesn't work.

For reference here's some more detail.
The formula in both conditional format and cell....
C$1 is the top of the column this formula is in.
Cell B9 contains the name of a row where the data to compare is located
relative_base is a named row that contains the column number of the scenario
I want to compare to
scenario contains the column number of the scenario I am in

To make things weirder, the above formula always has the conditional format
on while the formula below (same basic calculation just formated differently)
always has the conditional format off.

What am I doing wrong or what quirk of Excel have I found? Thanks for any

PS Does Excel 2003 have the conditional format limits (2050 rows) or is that
limitation gone now?

Does anyone know the expression to format text, say in cell A5, to blue if
the value of cell A4>=0 and to red if the value of cell A4

Here is my issue:
I want to color multiple cells based on certain criteria. For example,
I will select all cells F1:F12 and if the percentage in each cell is
less than the percentage in the same row (different column), color that
cell red. If greater, color it yellow. If =, color it white. I used
conditional formatting, and it is screwing up the colors based on what
I'm sure is my crappy logic. Please revise if you can. Thanks,

=IF((F12>C12), 1, 0) - Formatting color chosen is yellow

I have used some VBA script (see below) to conditionally format four cells. I now need to protect the worksheet and when I do this the formula in the cells works but the conditional formatting stops working.

Is this something I'm doing when protecting the sheet or is it the code that needs adjusting (and can it be adjusted??). (Please note I am a VBA newbie so adjusting my current code rather than giving me instuction on what to do would be really helpful )

Any help on this would be appreciated



    Dim keyRange As Range, tgt As Range 
    Dim oneCell As Range 
    Dim iColor As Integer 
    Set keyRange = Range("C66:F66") 
    Set tgt = Target 
    On Error Resume Next 
    Set tgt = Application.Union(tgt, tgt.Dependents) 
    On Error Goto 0 
    If Not Application.Intersect(tgt, keyRange) Is Nothing Then 
        For Each oneCell In Application.Intersect(tgt, keyRange) 
            Select Case oneCell.Value 
            Case 71 To 152 
                iColor = 4 
            Case 1 To 70 
                iColor = 43 
            Case -70 To -1 
                iColor = 46 
            Case -152 To -71 
                iColor = 3 
            Case Else 
                 'Whatever etc
            End Select 
            oneCell.Interior.ColorIndex = iColor 
        Next oneCell 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Does anyone know the expression to format text, say in cell A5, to blue if
the value of cell A4>=0 and to red if the value of cell A4<0? I know how to
do conditional formatting on cell A4, based on the value of cell A4, but how
do you format one cell based on the value of another?

Thanks in advance for your help!


Good Morning/Afternoon/Evening

I'm looking to conditionally format the cell colour of an entire row based on text entered in the last cell of the row. Excel's built-in conditional formatting is beautiful for this but I need six different formats (and three seems to be the max).

I did a search and found this helpful thread and I'm trying to adapt the code to my needs.

Here's what I've got so far.

Private Sub Worksheet_Change(ByVal
Target As Range)

   Dim Rng As Range
   Dim RowRng As Range
   Dim iColour As Integer
   MsgBox "Macro has been executed!!!!"

   Set RowRng = Range(Rng, Range("Rng").Offset(0, -9))
   For Each Rng In Target
      If Not Application.Intersect(Rng, Range("J1:J100")) Is Nothing Then
         Application.EnableEvents = False
         Select Case Rng.Value
             Case Is = "ahead"
                iColour = 3
             Case Is = "on time"
                iColour = 45
             Case Is = "behind"
                iColour = 50
             Case Else
                iColour = 0
         End Select
         If iColour = 0 Then
            RowRng.Interior.ColorIndex = xlNone
            RowRng.Interior.ColorIndex = iColour
         End If
      End If
   Next Rng
   Application.EnableEvents = True
End Sub
At one point in time, when I had set RowRng to a static range, the macro would successfully change the row colour whenever I changed a cell in J1:J100.

I then set out to modify my code to change only the row of the selected cell in the J column and came up with this part.

I was getting an error at this line, but now all of a sudden for some reason, the macro isn't even getting executed when I
enter text in the J column of the workseet. I know this because it isn't showing my message box when I change a value in the
J column.

What gives??

How can I conditionally format a cell based on the percentage value of
another cell rather than the value of its own cell?. All values ar

Example, I wish to format "A1" based on the value of B1. If B1 = -5% then
A1 would colour Amber, if B1 = -10% then A1 would colour Red, if B1 => 0
then A1 would go Green.

Three format to apply -
(1) B1 => 0% [Green]
(2) B1 <= minus 9.99% [Red]
(3) B1 between minus 0.01% & -9.99% [Amber]

Can this be done, possibly in one formula for conditional formatting?


I posted this thread in the VBA section as I believe it will have to be done with macros and not formulas.

I have always worked with conditional formatting on a basic basis. However, I'm trying to work with more advanced rules and I'm having trouble definining how I should approach it.

Currently on my spreadsheet I have conditional formatting on B18, which is dependent on a tier - 1-5 which is calculated in cell E20.


if E20 = 5 then condition formatting B18 0-104% = green, 104-105% = yellow, 105+ red
if E20 = 4 then condition formatting B18 0-119% = green, 119-120% = yellow, 120+ red
if E20 = 3 then condition formatting B18 0-124% = green, 124-125% = yellow, 125+ red
if E20 = 2 then condition formatting B18 0-129% = green, 129-130% = yellow, 130+ red
if E20 = 1 then condition formatting B18 0-134% = green, 134-135% = yellow, 135+ red

I really don't know how to set conditional formatting arrays, how exactly can I do this, I assume it has to be done with formulas, and not the conditional formatting GUI. Or does this need to be done in VBA? I really appreciate any help, I'm a little out of my experience here.

Now my experience with conditional formatting in VBA is limited - I only know basic conditional formatting in VBA such as..
Dim icolor As Integer

	If Not Intersect(Target, Range("A1:A10")) is Nothing Then

		Select Case Target

			Case 1 To 5

				icolor = 6

			Case 6 To 10

				icolor = 12

			Case 11 To 15

				icolor = 7

			Case 16 To 20

				icolor = 53

			Case 21 To 25

				icolor = 15

			Case 26 To 30

				icolor = 42

		End Select


		Target.Interior.ColorIndex = icolor

	End If
How exactly do I specify IF values based on another forum. I've used the LOOKUP formula, but I don't think that is applicable here.