Conditional Formatting help....

Hi All,
Background: I have a cell That I'm checking these three conditions
against.

B5= 100%, C5 = Blank

Three conditional formats:
=AND((B5-C5)>=1%,($B5-C5)<=9%)  (Background=YelloW (91% to 99%))
=AND((B5-C5)>=10%)                    - (Background =Red (below 90%))
=AND((C5>=B5)                            - (Background=Green (100% or
greater))

Problem: Before any data is entered the cells are all colored red
because the value in them is 0 by default. Is there a way to get around
this? I would like the user to enter a 0, have the cell check the
conditions, and then turn it red. Can this be done? 

Thanks for any help!


-- 
HellBent
------------------------------------------------------------------------
HellBent's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35341
View this thread: http://www.excelforum.com/showthread.php?threadid=551114

0
6/12/2006 5:28:47 PM
excel 39879 articles. 2 followers. Follow

4 Replies
598 Views

Similar Articles

[PageSpeed] 26

Use

=AND(C5<>"",B5-C5>=10%) for red

BTW, the last is just

=C5>=B5

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"HellBent" <HellBent.29av00_1150133406.2582@excelforum-nospam.com> wrote in
message news:HellBent.29av00_1150133406.2582@excelforum-nospam.com...
>
> Hi All,
> Background: I have a cell That I'm checking these three conditions
> against.
>
> B5= 100%, C5 = Blank
>
> Three conditional formats:
> =AND((B5-C5)>=1%,($B5-C5)<=9%)  (Background=YelloW (91% to 99%))
> =AND((B5-C5)>=10%)                    - (Background =Red (below 90%))
> =AND((C5>=B5)                            - (Background=Green (100% or
> greater))
>
> Problem: Before any data is entered the cells are all colored red
> because the value in them is 0 by default. Is there a way to get around
> this? I would like the user to enter a 0, have the cell check the
> conditions, and then turn it red. Can this be done?
>
> Thanks for any help!
>
>
> --
> HellBent
> ------------------------------------------------------------------------
> HellBent's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=35341
> View this thread: http://www.excelforum.com/showthread.php?threadid=551114
>


0
6/12/2006 5:45:05 PM
Hi Bob,
I tried that but it's still red. If I change the " " to a 0 then th
cell will turn white. Am I right that if a cell is empty then it equal
0?   

Thanks for getting back so fast

--
HellBen
-----------------------------------------------------------------------
HellBent's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3534
View this thread: http://www.excelforum.com/showthread.php?threadid=55111

0
6/12/2006 6:05:27 PM
I didn't say " ", I said "", they are different.

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HellBent" <HellBent.29awuo_1150135802.6635@excelforum-nospam.com> wrote in
message news:HellBent.29awuo_1150135802.6635@excelforum-nospam.com...
>
> Hi Bob,
> I tried that but it's still red. If I change the " " to a 0 then the
> cell will turn white. Am I right that if a cell is empty then it equals
> 0?
>
> Thanks for getting back so fast!
>
>
> -- 
> HellBent
> ------------------------------------------------------------------------
> HellBent's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=35341
> View this thread: http://www.excelforum.com/showthread.php?threadid=551114
>


0
bob.NGs1 (1661)
6/12/2006 6:39:00 PM
Ahhhhh! My bad Bob! Thanks again for your help!!!!!!

--
HellBen
-----------------------------------------------------------------------
HellBent's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3534
View this thread: http://www.excelforum.com/showthread.php?threadid=55111

0
6/12/2006 7:07:37 PM
Reply:

Similar Artilces:

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

Help with functions
If I have the following formula in a worksheet to bring up a message box =IF((F4-E4)>=10,Amend()+F4-E4,F4-E4) How can I use a function to transfer some information from different cells to a new worksheet if the formula above works? You can't have a function transfer information to another cell, it will return it's info in the cell where it's located, you would need a formula(s) in the cells where you want this information. Otherwise you would need VBA Regards, Peo Sjoblom "Monty" wrote: > If I have the following formula in a worksheet to bring up a me...

Help from Dave Peterson
Dave, Please see thread headed "Copying multiple sheets from one book 2 another and undertake special " dated 2/8/05. I have added some more text in there as I have finally got round to doing it and have experienced some problems. My name shows up as "Ann" rather than Pank as my good lady has been using my account. Thanks ...

HELP!!!!!!!!
I am getting alot of spam sent to my domain that does not have an actual email address. The problem is that the server then tries to send a message to the sender (who is bogus) with a delivery failure. My ISP has since shut down may mail once already. How can i stop the server from responding to mail coming in with incorrect adresses. I am using exchange 2k3 and windows enterprise edition 2k3. I am new to exchange and still in the process of learning it. Any help would really be appreciated. Are the SMTP addresses incorrect or are they blank? You can configure Exchange's Sender F...

Calculation / Format Error
I'm using this formula in cell AE69 =IF(AL69="NO","",AE68*(AM69/100)) When the logic is true I want the cell value to be zero, but show as blank. When this cell is summed up with others, a #VALUE! is returned. I am assuming excel is seeing this cell as text and thus returning an error. I have tried adjusting the formula to: =IF(AL69="NO","0",AE68*(AM69/100)) I have selected the accounting formating for AE69 with the "£" symbol. However, instead of getting the £ symbol to the left and a dash representing z...

Help please, can't start up!!
When I start the PC, after the loading bar for Microsoft thing finishes,instead of going to login screen it gives me a blue screen, and it says Page_fault_in_nonpaged_area. This just happened overnight!!!! Help please. :( -- Excessive Here are some hints ( 'Bug Check 0x50: PAGE_FAULT_IN_NONPAGED_AREA' (http://msdn.microsoft.com/en-us/library/ms793437.aspx) ). Try starting the system in safe mode (keep tapping F8 after power-on). -- whs Additional info: - I use Spyware Doctor scan every few days - I left my computer on for the night like always,and ...

multiple or conditions
I have written syntax that doesn't seem to be picking up all my or conditions thus leaving me with an inaccurate answer. What am I doing wrong? =IF($I5>2.5,1,0)*OR($K5>2.5,1,0)*OR($M5>2.5,1,0)*OR($O5>2.5,1,0) *OR($Q5>2.5,1,0)*OR($J5>5,1,0)*OR($L5>5,1,0)*OR($N5>5,1,0) *OR($P5>5,1,0)*OR($R5>5,1,0) Look in HELP index for the proper syntax for OR -- Don Guillett SalesAid Software donaldb@281.com "lschuh" <lschuh@discussions.microsoft.com> wrote in message news:E3DCD1CE-4350-40E8-B1C5-0A1CEFCD5B27@microsoft.com... > I have written syntax ...

Please help me solve my problem
Hi, I Have a cell say a1 that has a continuously changing number it in and I want to track the highest number that it prints, and the same for the lowest number in cell a2. I would like these results to show up in the cells below them Im very new to programming and your help would be much appreciated many thanks Sacha David On Mar 5, 1:01=A0pm, Sacha David <sach1...@googlemail.com> wrote: > Hi, =A0I Have a cell say a1 that has a continuously changing number it > in and I want to track the highest number that it prints, =A0and the > same for the lowest number i...

help need to match a string
Hi everyone Hope someone have a solution for me. I have a table with product name and price in column a & b in sheet1 I have the formula in sheet2 where I input the product name in A1 an it search the product and give me the price. =VLOOKUP($A$1,Sheet1!$A$1:$B$1377,2,0). It works ok. Here my problem: I have a product name called "HK-2859 plus" and I need this product t be identified if I enter any portion of this product ("HK" or "2859" o "plus"....). appreciate your help Thank -- Message posted from http://www.ExcelForum.com Hi one way: Use t...

Help needed with the last hurdle on this project
Hi I've finally reached the limits of my Excel knowledge (what littl there was) and need some help to complete my project. What's missing now is some automation to make what I need to do a lo more user friendly. My problem (for anyone generous enough to tackle it) is posted below. It's a lot of text but it's the most concise way of explaining what want to be able to do. I've attached a file with the Excel work as far as I have been able t do. Thanks to anyone who gives this a crack - it's very much appreciated. Martin PART 1 Take the data from Column A and Co...

SBS 2k and Exchange 2k Help
Hi All I'm having a problem with Exhange 2000. I support an office of about 5 people, and they are running SBS2000 with Exchange 2000, during the past couple of days they haven't been able to send/receive e-mail, and they haven't been able to browse the internet. I initially thought the server was being used for Relaying, however the queues are not very high (about 14 e-mails) and the Telnet tests suggest it can't be used for relaying. When I got there last night the server was fine and after a quick re-boot I sent numerous e-mails to my home account which I received, and I...

conditional criteria in DSUM
I have a large spreadsheet of data containing part numbers. The part numbers are entered as numbers and then custom formatted to display dashes (i.e, part number 1234567890 displays as 123-456-7890). I am trying to use a DSUM formula that will add the beginning inventory (rows) for a month (column) for a particular group of parts. For example, all parts that begin with "3002" (i.e., 30020007409). I cannot for the life of me get the criteria to recognize the part number. I have tried criteria such as ">30020000000", and "3002*". Nothing is working. ...

[Help] OnPaint Skipped?
I have an CTestWnd defined as follow class CTestWnd : public CWnd { DECLARE_DYNAMIC(CTestWnd) public: CTestWnd(); virtual ~CTestWnd(); protected: DECLARE_MESSAGE_MAP() public: afx_msg void OnPaint(); }; and i create an instance of CTestWnd as a child window of hWndParent LPCTSTR lpClassName; lpClassName=AfxRegisterWndClass(CS_VREDRAW | CS_HREDRAW |CS_GLOBALCLASS, ::LoadCursor(NULL, IDC_ARROW),(HBRUSH) ::GetStockObject(WHITE_BRUSH), ::LoadIcon(NULL, IDI_APPLICATION)); RECT rect; ::GetClientRect(hWndParent, &rect); m_Wnd.CreateEx(NULL,lpClassName, _T("TEST"...

Date formatting in Excel #2
How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I type 10-4, Excel returns Oct 05. Hi When no year is typed, excel would assume it is the current year. You would ahve to type the full date (including 2004) for it to display as Oct 2004. Use Format - cells - date and then select the particular format you want from the options there. >-----Original Message----- >How do I format a cell to return Oct 04 when I type 10-4 in Excel...When I >type 10-4, Excel returns Oct 05. >. > ...

VBA Help #4
Hi, I have a list of clients with their associated outstanding dollar amount and their return (%). I would like to know if there is code that would allow me to break up these clients by return (%) groupings. For example, I would like to group below 10%, 10% to 17%, and above 17%. So at the client on the list that is just before 10% I would like to add two rows and put a dollar subtotal in the immediate row and sum the dollar amounts. Can anyone help? Thanks, Marino Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastR...

Conditional Autofill
Hi all! I've got this problem and it's really bugging me!! Say, I've got Sheet 1 (attached) and there's some formula (assume it' some VLOOKUP formula) on D3. I basically need some macro that woul autofill the formula in D3 down to all the other cells in column D EXCEPT for those cells with no corresponding value in the B column an those with "Total". Any help would be greatly appreciated. Thanks +------------------------------------------------------------------- |Filename: Sheet 1.JPG |Download: http://www.excel...

Nightmare Bug. !! Plz Help
hi all at once when i try to edit or open some forms i got such error msg "There isn't enough memory to perfrom this operation. close unneeded programs and try the operation again." I have 1GB Ram so it is sure silly msg !! what to do thanks plz help -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1 What version of Access are we talking about here? By any chance does this involve moving from one version to another? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2...

display cell value in msgbox formatted as %
Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple...

We would love To Help You With Your Microsoft Dynamics GP Issues
If you are having issues with Microsoft Dynamics GP- reports, need training, upgrades, ongoing support, or anything of the such- please feel free to email us at mtatum@integritymbs.com, or call us at 888.869.4090 ext 705. We would love to assist you. We provide install, implementation, ongoing services, training, customization, report writing, troubleshooting and much more. ...

Copy/Paste to Match Destination Formatting -- BY DEFAULT
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel As a long time PC Word user, I have been accustomed to the large amount of preference customization that is available. One CRUCIAL (for me) customization option in PC Word is the ability to set the default action when pasting from one program (the web or Acrobat) to Word. <br><br>By default, both Word 2008 (mac) and Word 2007 (PC) keep the source formatting when pasting between programs. To me that makes no sense, because 9 out of 10 times you don't want your word document to have many differ...

Help, my views got messed up and "folder options" is now restricted
I have no idea what has happened here, I think vista somehow messed something up when I connected to a file share, but even that seems odd since I've been doing this for 6 months. I'm using Windows 98SE and have files shares on a LAN to a Vista 64 Home Premium machine. I can't choose "folder options" in the My Documents folder or any random subfolder. When I try to do so, I get a message "This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator". I _AM_ the administrator, and there i...

passing dates in a conditional sum(if)
I have a workbook with 5 sheets. Sheet1 is Customers Sheet2 is YTD Sales 07 Sheet3 is Total Sales 06 Sheet4 is Total Sales 05 Sheet5 is Total Sales 04 Customer sheet has this basic formula in the columns for each year's sales based on the customer's number and the variable of the month that the sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))} The problem is that the accounting program exports the invoices with the MMDDYY for each invoice. The $j$1 is the number of the month I limit it up to. I have a column in each sales sheet with the month() function to...

Find and loop help-multiple columns
I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Fi...

Duplicate messages ++++ HELP....
I am using Outlook 2002. Setup with one email account only as on offline user to 5.5 Exchange. When I do the send/receive via dial up I pull down whatever messages are on the Server down to the .PST file. Yet when I dial in again I get the same messages over and over. Again I only have one email account setup on the laptop. And it is setup to work offline with a .PST file. Everything I have read and found pertaining to this is if you have multiple acounts setup under the send/receive. This is not the case here. Getting tired of Microsofts constant change and lack of documentation of ...

deleting a conditional format
I have the followint formula in a conditional format. =ROW()=ROW(INDIRECT(CELL("address"))) I remember getting it from this group, it highlights the active cell in yellow. I cannot delete it and I believe it will not allow me to copy and paste data about the spreadsheet. I need to delete as I need to copy and paste more than I need the active cell highlighted. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany <mark@southwestconst.com> schrieb im Newsbeitrag news:1102362753.480803.96910@c13g2000cwb.googlegroups.com... > I have the followint formula in a con...