Free Microsoft Excel 2013 Quick Reference

- Conditional Formatting if greater than zero
- Conditional Formatting with 3 options
- Conditional Format
- Conditional formatting in an adjacent cell
- Conditional formatting on Main report based off sum control on the subreport
- Conditional Format based on forumula result
- Conditional Formatting - 2 Worksheets one Named Range
- Applying conditional formatting to cell based on another cell's in
- Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...)
- Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...)
- Value of Less Than Zero to Equal Zero
- Conditional formatting with 6 conditions?
- Conditional Formatting a cell based on the value in a different cell
- VBA code instead of Conditional Formatting
- Conditional Formatting Bug?!!!
- #DIV/0! and CONDITIONAL Formatting: ?
- Conditional Formatting problem...
- Quick Conditional Question
- Format of one cell conditional on another
- 4 conditional formats, can it be done?

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.

thanks

Mark

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.

Thanks

jledlow

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.

=IF(P5>0,VLOOKUP(A5,Variance!C:M,10,FALSE),IF(L5

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.

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200701/1

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!!

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?

Thanks

--

shadestreet

------------------------------------------------------------------------

shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092

View this thread: http://www.excelforum.com/showthread...hreadid=563687

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?

Thanks

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

mts0628@mail.ecu.edu

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.

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.

Thanks!

=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:

VB:Range) 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 'Whatever End Select Target.Interior.ColorIndex = icolor End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!

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?

=OR(B52=0,C52>B52,C52/B52>=$J$35,C52/B52=#DIV/0!)

=OR(AND(F22>$C$6,ISNUMBER(SEARCH("downstream",$B$8))),AND(F22>$C$4,ISNUMBER(SEARCH("upstream",$B$8))))

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?

=OR(AND(G22

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

greater than zero

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.