Free Microsoft Excel 2013 Quick Reference

Conditional formatting if greater than zero Results

I would like to highlight the smallest value in cells E3:Q3 that is greater than zero what formula do I need?

Hi, i would like to know if anyone has successfully created a conditional format with 3 options whereby 3 colours are displayed depending on the cell/formula result contents. I would say i have very strong excel skills and although i could easily program this in VBA the spreadsheet needs to work without VBA in this instance.

basically my problem is to monitor changes in performance between 2 dates for a budget whereby a positive improvement of actual vs budget is one colour a negative performance is another colour ( now that was the easy bit ) the difficult bit is to show a third colour when the variance value being coloured is positive but is still less than they year to date value.

for example

Plan May ytd = 70
Actual May ytd = 39
Variance ytd = (31)

Plan Feb ytd = 43
Actual Feb ytd = 9
variance ytd= (34)

therefore there is a +3 improvement in the ytd variance, but overall the variance is still negative.

it is this condition which i need to trap.

it would be easy to use more than one column but it all needs to be displayed in just one with 3 colours indicating the condition ( effectively there are 4 with 'blank' background being a positive or static change in performance and ytd is still positive )

at the moment the conditional formating formula is quite simple for the +/- state with it just being coloured with a if greater than zero or less than zero option.


If I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use.
I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.

I am creating a drawer balance sheet for a business. I have one cell, lets assume it is "H11", that will have three conditions which are: 1. greater than zero, 2. equal to zero, and 3. less than zero. What I want to accomplish is I want to have cell "G11" to display different text depending on the condition of h11. For example if H11 is greater that 0 I want G11 to display OVER. If H11 is equal to zero I want G11 to display IN BALANCE. and if H11 is less than zero I want G11 to display SHORT. Any help would be greatly appreciated.


I have a report that has a subreport included. I am trying to use conditional formatting on the main report if the sum control on the subreport is greater than zero. For some reason the conditional formatting is a record off. It works when the prior records subreport is greater than zero. I have 2 groups and the subreport is linked correctly. I have the sum control on the subreport in the report footer and the conditional formatting is on the second group. Any ideas?

I have a column with values, some zero and some greater than zero. These
values are the result of a formula. Whenever the value is higer than 0, the
conditional format wil highlight that value bold & blue.

Now, i've changed the formula with a VLOOKUP. It looks for a value in
another worksheet, if it doesn't find it there, it will use another formula
and use that value.


Hi All,

I have two worksheets using numeric values:

Sheet1 Range N17:N100 (84 Rows)
Sheet1 Range K17:K100 (84 Rows) - If condition is met apply Strikethrough
Conditional Format
Sheet2 Range K197:K280 (84 Rows) - Named Range "YR2006"

I would like a Conditional Formula to set criteria below and apply CF:
IF a cell value in Sheet1 N17:N100 is >0 (greater than zero) AND >= (greater
than or equal to) its corresponding cell value in Sheet2 Named Range "YR2006".

The cells in Sheet1 Range N17:N100 should be compared individually to their
corresponding cell in Sheet2; ie. Sheet1 N17 is compared to Sheet2 K197,
Sheet1 N18 is compared to Sheet2 K198 and so on.


Message posted via

Is this even possible? I've done conditional formatting to a cell based on
that same cell's info BUT what I want to do now is something like this...Can
I apply conditional formatting to cell A3 based on the information in cell
B3? If so, how do I do that?? I completely suck at writing formulas so, if
you know the answer, can you help me with how to write the formula, too?
Basically, I want to apply formatting to A3 if B3 has a value greater than
zero. Thank you!!

Two questions:

1. Can I have a conditional format that is dependant on two different
cells? For example in Cell A2 I want to have the cell turn bright red
if A2 is greater than zero and B2 equals zero. I want to then drag
this down for every cell in column A.

2. I cell C2 I want to turn this cell bright red if cell D2 gets the
error message "DIV/0!" to flag there is an error. Is this possible?


shadestreet's Profile:
View this thread:

Two questions:

1. Can I have a conditional format that is dependant on two different cells? For example in Cell A2 I want to have the cell turn bright red if A2 is greater than zero and B2 equals zero. I want to then drag this down for every cell in column A.

2. I cell C2 I want to turn this cell bright red if cell D2 gets the error message "DIV/0!" to flag there is an error. Is this possible?



I am trying to format my answers in cells of a worksheet.

I have an equation that uses addition, subtraction, multiplication and division. This equation has dependent variables in other cells and when these cells are filled in with data, the original equation yields a number. Unfortunately, if one of the cells is not used, there is still a value reported by the original equation.

As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the result will be equal to 5.5. But if cell A2 does not have a value, the value will report -0.5. Here is the problem. Well, I think I have two problems.

The first is, how do I make my function compute only after all of the values are entered?

And second, and maybe harder, is how do I make any values of a number display zero if the result is less than zero? I tried using the IF Logic function and using the following:
Logic Test: B2<0 (where B2 is the cell with the equation)
Value if true: 0
Value if False: B2 (I wanted the actual value to be reported if the number was greater than zero)

This attempt was hit or miss at best. If the value was less than zero, it would report zero; this is what I wanted. But if it was not less than zero, then it would still report zero. Bummer.

If this second problem could be fixed, then the next thing that I would ask is how do I apply that same IF Logic function to other cells? (as if I set the conditions of the IF Logic function to one cell in a column and I wished to apply them to subsequent cells in the same column).

I hope this isn’t too confusing.

Thank you in advance for any help that can be offered.

Best regards to all,
Thomas Styron

I'm using Excel 2000 which has a limit of three conditions, I have 6. If you could just get me started, I still don't understand VBA enough to do this.

In cell A1, the color is set to red. I want A1 to turn green when all 6 conditions are met:

1. Cell B1 has a valid value from its pull down list, no other value, and not empty.
2. Cell B2 has an integer, no decimal places allowed, and not empty.
3. Cell B3 has a valid value from its pull down list, no other value, and not empty
4. Cell B4 is not empty.
5. Cell B5 has a three decimal place number greater than zero.
6. Cell B6 has a single letter from A-Z only.

Please see the attached workbook.

Thank you for your continued help.

I'm trying to change the cell color to yellow, if a value in another cell is greater than zero.

Cell A1 (Employee Name)

Cell C211: 125

I've tried the formula: =IF(C211>0,"")

But it's not working.

I'm using Excel 2002.


Using conditional formatting, I set the font color to Green for Cell G12
=IF(SUM(G12) > C28,SUM(G12),"")

This worked. However I needed 6 conditions for this formatting. Excel only allows for 3 conditions.

My VBA code is not working. Can someone tell me the correct code? Many thanks in advance.

For cells "G12,O12,G23,O23" I want the font colors to change if their value is
1. greater than or equal to Cell C28 (change to green font)
2. greater than or equal to Cell C29 (change to orange font)
3. greater than or equal to Cell C30 (change to brown font)
4. greater than or equal to Cell D28 (change to green font)
5. greater than or equal to Cell D29 (change to orange font)
6. greater than or equal to Cell D30 (change to brown font)

It is set so that if a value is enter in C28, D28 will have a zero value. Can only use one or the other. Likewise if C29 has a value, D29 will be zero and cannot be used.

The code I used was:

    Dim icolor As Integer 
    If Not Intersect(Target, Range("G12,O12,G23,O23")) Is Nothing Then 
        Select Case Target 
        Case >="C28" 
            icolor = 9 
        Case >="C29" 
            icolor = 45 
        Case >="C30" 
            icolor = 52 
        Case ="D28" 
            icolor = 9 
        Case ="D29" 
            icolor = 45 
        Case ="D30" 
            icolor = 52 
        Case Else 
        End Select 
        Target.Interior.ColorIndex = icolor 
    End If 
End Sub 

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

I have an excel spreahsheet with the following info:

Cell C2: 38
Cell D2: 76
Cell E2: =IF(C2=0,100&"%",ROUND((D2-C2)/D2*100,1)&"%")

The weird formula is to get rid of divide by zero errors.

Cell F2: 90.0%

What I want to do is conditionally format Cell E2 to have a background color of red when it's contents are below 90.0% (or Cell F2's contents), whichever is easier to deal with. At this point I can get that to work, but here's where it gets tricky...

It doesn't matter if I try to add another condition to make the cell turn white when it is greater than or equal to 90.0% or not, it (Cell C2), will turn red when the cell's contents are 100%, which is obviously greater than 90%.

I can get this to work with the cell's contents at any value other than 100% or 100.0% (I've tried both), also, if the cell contents go above 100% it also is painted red.

This is weird!

I've tried to use the if formula = true in conditional formatting to do the formatting to no avail, I've tried making sure the number formatting is all matched to the conditional formatting entries, I've tried just about everything I can think of...

Is there a way to format cells in formulas, and if so, where might there be a link explaining all the formatting codes that I might need?

TIA for any help on this!

Is there a way to use #DIV/0! in conditional formatting?

I have "some" cells in column B (the divisor) that equal zero or are less than the number in column C.

I want to the cell to turn green if the zero is in column B or the number in column B is less than the number in column C or the result of the division between C/B is greater or equal to 90%.

I ahve everthing worked out except when the result is #DIV/0!

Is there anyway to do this "without" adding a hidden column?


I've got this OR/AND conditional formatting formula in all cells across 4 columns:


It works great. When I apply my pivot filter in $B$8 and choose either "upstream" or "downstream" it correctly colors my pivot table cells 'Red' as specified by my conditional format criteria, if the cell value is also greater than > than the absolute reference cell threshold value in $C$6. Great, right?

But when I add a field in my pivot table where I need it to look at a different absolute reference cell value, which I need it to again color the cell G22 'Red', IF the cell value is less than < than the absolute reference cell threshold value in $D$6, suddenly the formula doesn't work properly. It shades some cells 'Red', that are lower in value than $D$6, and some that are higher. It also shades rows 'Red' outside the pivot table area...

Here's my formula that doesn't work (see below). I think it has something to do with the condition if the cell value is zero '0' or 'blank'. I think I need to make this formula more robust to accomodate those 2 conditions. But then again, why did the first formula condition work perfectly?


I'm trying to setup a conditional format so that if cell A4 is greater than zero and cell M4 is empty to show a red background.

Can anyone help with the formula?

Is there a way I can make a cell, eg B10, bold if another, say A10, is
greater than zero

I need 4 conditional formats on one cell, un less there is another way of doing it!?

Excel is giving me 3 maximum?

Basically if the value falls above 5 or below -5 it needs to change the cell red, if it is inbetween 5 or -5 then orange, UNLESS it is zero then I need green really??????

AT the moment I have used 1 for the greater than or equal to 5, 1 for the less than or equal to -5, and 1 for inbetween -5 and 5.....

Hope that makes sense, thanks

No luck finding an answer? You could always try Google.