 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2005Conditional Formatting
Am using Excel 2000 and want to have Conditional Formatting in cells. I want cells that are greater than a number to turn green. The cell I am Conditional Formatting also contains an 'IF' formula and turns green whether there is any number in it or not. Is there any way I can get it to Conditional Format only when a number appears in the cell? Hope I have made myself clear Nikwak I used A1 for my cell. Format|conditional formatting: formula is: =AND(ISNUMBER(A1),A1>5) Change 5 to what you want. > Am using Excel 2000 and want to have Conditional Formatting in cells. > [quoted text clipped - 7 lines] > > Nikwak  SignatureDave Peterson Nick, This should do it for you. Conditionl format in the "Formula Is" option =AND(A1>?,A1<>"") This is where A1 is where your IF statment is and also your where the conditional format is. ? is the number that you want A1 to be greater than to turn the cell green. Cheers, Steve SignatureSteveG Thanks guys - that's been a huge help Nikwak > Am using Excel 2000 and want to have Conditional Formatting in cells. > [quoted text clipped - 7 lines] > > Nikwak Oops! Still a problem: I don't think I explained it really clearly - got myself confused, so will break it all down in the hope it makes more sense: The spreadsheet has a column whereby there are about 120 entries. All these entries have to go either Green, Red or Yellow if they hit a certain target (eg red if they are less than 5, yellow if between 5 and 10 and green if over 10) However, the formula in these cells, to find out the value, is something like =IF(H17<>0,H17/G17,"") The red and the yellow conditional formatting seems to work - as does the green but if, as in above example, H17<>0 then the workout cell turns GREEN, when it should in point of fact be RED as it is less than 5. Hope that explains things a bit clearer and that someone will instantly recognise what is going wrong Nikwak > Am using Excel 2000 and want to have Conditional Formatting in cells. > [quoted text clipped - 7 lines] > > Nikwak > Oops! Still a problem: > [quoted text clipped - 17 lines] > > Nikwak H17<>0 is not a valid test. You can have H17<0 or you can have H17>0 but not both together. If you really need both conditions you must have two separate tests combined using the OR function. However, unless you have negative numbers, H17>0 alone may be quite adequate. Depending on the circumstances, you may need to use instead H17>0.001 or something similar, to avoid an extremely small number (resulting from a rounding error) triggering the condition. H17<>0 is a valid test for "H17 not equal to zero". > H17<>0 is not a valid test. You can have H17<0 or you can have H17>0 but not > both together. Ok. In your first post, it appeared that if the cell was blank, you did not want to have any fill for the cell. The formula you just used as an example returns a blank if H17 does not = zero so therefore, it is not less than 5, there is no value in the cell per say when it comes to the conditional formatting unless you omit the AND F17 <> "". With the last proposed solution I sent, it is telling it to color only if the cell is less than 5 and also not blank. I just used these three conditions and it changed correctly for me sticking to your first post where a blank cell should not be filled. F17 is where I entered the conditional format and used your IF statement that refers to H17 and G17. 1. Formula is: =AND(F17<5,F17<>"") ---Format Red 2. Formula is: =AND(F17>=5,F17<>"",F17<=10) ---Format Yellow 3. Formula is: =F17>10 ---Format Green Does that help? Steve SignatureSteveG I think I got lost somewhere. Are your conditons being based off the result of your formula in cell J19 or are they being based off the values in H19 or G19? If it is the resulting value of the formula in cell J19, the condition will always turn green in Stephen's last post if the formula returns a blank. That is because the condition views the formula in the cell as a value greater than 0, 8 or 10. You need to accomodate for the result of that using the AND function. To your Green and Yellow conditions add. =AND(J19>10,J19<>"") ----Green =AND(J19>8,J19<>"") ----Yellow Continuing with Stephen's post for the Red condition, that will turn J19 Red if the value in J19 equals "". If you want it to be clear, apply the same method as above to the Red condition. =AND(J19>0,J19<>"") ---Red HTH Steve SignatureSteveG Stephen Thanks for that. The formula to get rid of '#VALUE!' etc is ok, that seems to work but I take on board what you have pointed out. It is the Conditional Formatting that seems to be picking up the fact that there is a number in the cell that is equal to or greater than 10. For instance, I have in cell J19 the formula =IF(H19>0,G19/H19,"") BUT I have Conditionally Formatted cell J19 to turn it red if value is under '9', yellow if between '9 and 10' and green if '11' or over. I think it is picking up the number '19' and therefore turning the cell green. What I need to do is, if there is no actual value showing in the cell (ie '0' or under) that it either remains clear or turns red. Hope that explains it clearer Nikwak >> Oops! Still a problem: >> [quoted text clipped - 25 lines] > to avoid an extremely small number (resulting from a rounding error) > triggering the condition. Sorry, I misunderstood and I'm still not clear about what you mean by "it is picking up the number 19". Anyway, my suggestion is that you apply the conditional formatting in the order green, yellow, red. That is (and thinking only of integers for simplicity), first test for H19>10 (green). The second test is for H19>8 (yellow); note that it doesn't get to this test if it has gone green, so H19>8 means that it =9 or 10. The third test is for H19>0 (red); by similar logic this means 1 to 8 inclusive. Anything else (0 or negative) will not trigger the conditional formatting, so will leave the cell clear. > Stephen > [quoted text clipped - 46 lines] >> similar, to avoid an extremely small number (resulting from a rounding >> error) triggering the condition. Stephen Thanks for this but I am now as confused as I have made everyone else - sorry! What I meant by "it is picking up the number 19"was that I thought the conditional formatting was picking up the fact that there was the number 19 in cell J19 and therefore the conditional formatting was seeing the number 19 and thinking it was over 10 so showed Green. I am confused as to what the formula should now be in cell J19, which, at the moment has =IF(H19>0,G19/H19,"") with the conditional formatting side of it done through Format/Conditional Formatting on the toolbar. Nick > Sorry, I misunderstood and I'm still not clear about what you mean by "it > is picking up the number 19". [quoted text clipped - 60 lines] >>> similar, to avoid an extremely small number (resulting from a rounding >>> error) triggering the condition. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.