Hi everyone!
This is an easy one for you Forum Geniuses!!
Simply put, I'm trying to make an IF statement that If the value of this one cell is less than the other, I'd like it to turn green, if the value from that one particular cell is more, then I'd like it to turn red.
Your help is greatly appreciated!
Last edited by HHR; 09-27-2012 at 12:29 PM.
Use conditional formatting. "Less Than" is one of the standard "Highlight Cells Rules". In your case you'll want to use two rules- one less than and one greater than.
SPARTAN
Please click the * if my solution helped
Use Conditional Formatting with the ForumulaIs option. See conditional formatting in Excel Help
Gary's Student
There is no formula for this?
I'll try your suggestion but would rather use a formula.
I'm sorry, I'm very excel illiterate.
Attached is what I'm trying to do.
I'd like the cell color to change in D15. If the value of D15 is greater than A15, then I want it to change red, if not, green.
I've tried the conditional formatting but it ONLY stays red, regardless of what rules I put.
See attached.
You need 2 conditional formatting statements. I deleted the ones that you had in there. You can use formulas in conditional formatting but in this case you do not have to. (Formulas within cells do not ever, by themselves, affect cell formatting).
1. New Rule> Format only cells that contain> [Cell Value] [greater than] [=A15] Format as red, OK
2. New Rule> Format only Cells that contain> [Cell Value] [less than or equal to] [A15] Format as green, OK
See attachment
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi HHR,
If you want it in macro then please see attached with macro.Colour cell.xls
Wonderful!! The color scheme worked and I added Yellow as well.
Now I can't get the DIV/0 to go away - any ideas how to get this as a zero when the other cells are blank or have a zero?
Since you're using Excel 2007, you can use the IFERROR function. In E2, for example
=IFERROR(SUM(((C2/D2)*100)-100)/100,"")
Does that work for you?
ChemistB - it worked like a charm!! I can't thank you enough!!!!
I think an easier formula to get rid of the DIV/0 is to use =if(D2=0,0,formula), where D2 is the denominator in the formula. So if the denominator is 0 the result entered will be 0 and if the denominator is anything else then the result is the intended formula.
Re: Post 13
Guess it depends on what you are comfortable with.
=IF(D2=0,0,Formula) extra keystrokes 11
=IFERROR(Formula,"") extra keystrokes 12
IFERROR Will catch any type of error, ie if the operator puts text into C2 while IF focuses on the #DIV/0 error
Like HHR, I am very Excel illiterate. Is there a way to do this based on a range of cells? In the attached form, I will enter 12 values, one for each month of the year. I would like to have the worksheet automatically highlight the largest value within the range (in the example, the value entered for June). in different examples, other months may be the higher value so I will need to format each cell to compare to the rest of the values in the range. Thanks in advance for the help!
Select your entire range (i.e B2:M20) Conditional Formatting > Use Formula
=B2=MAX($B2:$M2)
Format color as required.
Does that help?
Thank you ChemistB, took me a few minutes, but I got it working. appreciate the help!
Now, let's see if I can take this one step farther.
once I have identified the highest value, I would like to have row 3 show the result of the row 2 value divided by the highest value. Do you know of a formula that will perform this function? thanks again
Last edited by jmansell; 09-27-2012 at 04:22 PM.
=b2/max($b$2:$m$2) ?
You are awesome! that does it. thank you again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks