Conditioal formatting problem

I have a column with conditional formatting applied.  Say I'm formatting
cell E4.

The column shows the number within a processing cycle which need
warning traffic lights when certain days are reached so far I have set
up:

Condition 1 shows orange if the cell value is between 9 and 11.

Condition 2 shows red if the cell value is greater than or equal to
12.

My problem is that I need a 3rd condition to grey out cell E4 if a date
is entered into cell G4.  I've tried all sorts but the formatting will
not work.

Firstly can someone suggest a formula for condition 3.

Secondly do I actually need formulas in conditions 1 and 2 to state
apply formating as per the criteria above , but only if G4 is blank?

any guidance would be greatly appreciated.

Thanks


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

0
9/22/2005 12:17:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
600 Views

Similar Articles

[PageSpeed] 0

Put your 3rd condition 1st, using this formula: =AND(ISBLANK(G4),E4<9)
I didn't use formulas for the 2nd and 3rd conditions.
************
Anne Troy
www.OfficeArticles.com

"HDV" <HDV.1vrhea_1127394305.0938@excelforum-nospam.com> wrote in message 
news:HDV.1vrhea_1127394305.0938@excelforum-nospam.com...
>
> I have a column with conditional formatting applied.  Say I'm formatting
> cell E4.
>
> The column shows the number within a processing cycle which need
> warning traffic lights when certain days are reached so far I have set
> up:
>
> Condition 1 shows orange if the cell value is between 9 and 11.
>
> Condition 2 shows red if the cell value is greater than or equal to
> 12.
>
> My problem is that I need a 3rd condition to grey out cell E4 if a date
> is entered into cell G4.  I've tried all sorts but the formatting will
> not work.
>
> Firstly can someone suggest a formula for condition 3.
>
> Secondly do I actually need formulas in conditions 1 and 2 to state
> apply formating as per the criteria above , but only if G4 is blank?
>
> any guidance would be greatly appreciated.
>
> Thanks
>
>
> -- 
> HDV
> ------------------------------------------------------------------------
> HDV's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=26299
> View this thread: http://www.excelforum.com/showthread.php?threadid=469842
> 


0
ng1 (1444)
9/22/2005 1:15:39 PM
In Condition 1, use Cell Value Is between 01/01/1900 and 31/12/2100

Condition 2, Cell value is between 9 and 11

Condition 3, cell value is greater than or equal to 12


This works as long as there are no dates enter which are before
01/01/1900 and later than 31/12/2100.

Hope this helps.
Jase


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

0
9/22/2005 2:02:30 PM
You need to change the order of your conditions.  Check for the status
of G4 in cond. 1:

Formula is: =G4<>""  set your fill pattern color to gray

cond. 2 (will only be used if cond. 1 is false)

Cell Value Is:  >12   set fill pattern to color RED (may also want to
change font color to yellow for visibility's sake)

cond. 3 (will only be used if both cond. 1 & 2 are false)

Cell Value Is: Between: 9  and 11   set fill color to orange


HTH


-- 
swatsp0p


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

0
9/22/2005 2:04:15 PM
Reply:

Similar Artilces:

Formatting Excel to export to Outlook
How do I format Excel to be able to export it to Outlook? Hi the easiest way would be to first export from Outlook to Excel and have a look at the datasheet layout -- Regards Frank Kabel Frankfurt, Germany "rgonzalez" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:43c401c49042$9bea4780$a601280a@phx.gbl... > How do I format Excel to be able to export it to Outlook? ...

problem viewing shared outlook 98 calendar
I'm trying to share/view my home calendar (home pc w/win 98/outlook98) with my work outlook 2000 pc/win 2000. I've done everything right for my home pc & received the invite via email to my work email & accepted; however, whenever I click on the "home calendar" link @ work, my calendar is blank. None of my appmts show up! Can anyone help! I work at both places but have my pda synced w/my home email account & do not want to remove all this data & reinstall so that my pda syncs with my work pc. Hope this makes sense! ...

ISBLANK problem
Hello Everyone, I was looking out for some help in my excel sheet. I have a formula : =IF(ISERROR(VLOOKUP($D9,HPEQV1!$D:$E,2,0)*VLOOKUP($D9,'base data'!$D: $IA,J$1,0)-VLOOKUP($A9,Counterfeit!$X:$IA,Calc1!J$1,0)*SUM('base data'! J9:J12)),"",VLOOKUP($D9,HPEQV1!$D:$E,2,0)*VLOOKUP($D9,'base data'!$D: $IA,J$1,0)-VLOOKUP($A9,Counterfeit!$X:$IA,Calc1!J$1,0)*SUM('base data'! J9:J12)) Now this formula obviously gets a number generated. I have put an iserror to have a blank. Actually what I would like to do is .. where ever the result is a zero or a blank .....

Conditional format for Highlight first Minimum Value greater than zero
Hello All, I am using Office 2003 and have the following problem I am trying to use CF to highlight the first Minimum value greater than zero. I selected the range with A10 as active cell and used =A10=MIN($A$10:$A $15) in CF but this highlights Zeros I also tried =AND(A10>0,MIN($A$10:$A15)) but did not succeed Can anybody point me in the right direction TIA Rashid Khan Hi Rashid, Try this formula =A10=MIN(IF($A$10:$A$15<>0,$A$10:$A$15)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <prkhan56@gmail.com> wrote in m...

weired problem...
My exchange server 5.5 is running in Win2k AD. To migrate this to exchange 2000, I installed a new additional DC and set the role of a global catalog server. I also installed ADC to prepare E2k installation in the same machine. Then I run e2k setup with forestprep and selected option to join existing exchange 5.5 site, also supplied server name and admin account. In the next screen,it displated the username with invalid domain name (e.g. username@domain.com/) in the box and asked for the password for admin. I entered the password but got message "invalid user name" and stop...

Border Formatting Disappearing when data entered into cell
I have an Excel Worksheet that is formatted with borders and shading. When I type into a cell, or paste into a cell, the formatting disappears, and seems to revert to some default value. Can anyone help explain how to make Excel act like iot's supposed to? ...

Not recognized format
I've written large workbooks with excel xp, but at home I am using excel 2000. Is there any way to convert the xls file format that xp uses so I can work with the spreadsheet at home? Thanks, Alan There's no difference in file format for this version (xl97 to xl2003 share the same fileformat). There may be some features/macros that won't work because of the difference--but you should be able to open the workbooks. SKLwater wrote: > > I've written large workbooks with excel xp, but at home I am using excel > 2000. Is there any way to convert the xls file form...

Interesting Problem with Child element order
Hi there, I have an interesting problem that maybe you pros can suggest how I solve. I'm working with a third party program that serializes an XML document (it was obviously not designed with schema in mind). I created a schema from this document. It works fine. Except for some unknown reason, in a small part of the XML document, this program switches the order around, and of course the validator I built then fails. Its always the same two elements it switches (from top to bottom and bottom to top) ex: Some documents have the metaID on top, some on the bottom, these are the only two e...

Problems with sidebar after logon
Hi, We are using Windows domain with Windows Server 2008 Std R2 DC. We have currently several Vista desktops which are in domain. DNS is configured as MS Best Practices suggests and there are no other GPOs in use than Default Domain Policy. After successfull logon the sidebar does'nt fully load (sidebar appears but clock doesn't show up). Also if you try to start MS Offce Word it get stuck in loading screen. If you wait nothing happens. Wierd thing is that every other program will load normally (even other Office programs) but Word and Sidebar are stuck. All this...

prevent auto convert format
I use program language dump a table in database to a csv file, some fields' data type are "char";however,it's comprised of numeral, like "06885341".when I open the csv file with excel, the "0" in the front of string disappeared. How can I prevent it happen? -- Zane Excel will always automatically convert numeric looking data to true numbers when you open a CSV file. To stop it you must change the file's extension to something else, like TXT. Then when you open the file in Excel the Text Import Wizard will start up automatically. Use that to ...

Tagging formatting (cells with mixed formatting)
I need to tag all formatting (bold, itlaic, bold italic, superscript, underline, etc) in all the cells throughout a worksheet. The way I am doing it is looping through each cell of the UsedRange and then through each character of each cell. I am wondering if there is a better/faster way to do this. Any help would be much appreciated. Here is what I have so far, I still have to add the Else If statements for all the other formatting: For Each myCell In rng.Cells myBold = myCell.Font.Bold If myBold = False Then 'do nothing ElseIf myBold = True Then 'tag whole cell m...

Customer Mailing List
Whats the best way to output the customer mailing list so that it doesn't require the entire content to be reformatted before printing out mailing labels? If exporting to a word .doc it lumps everything up in one column. Is there a better way w/out having to virtually reformat/retype all of the customers? Thanx in advance! Rich Export as a CSV, then use Mail Merge to load the fields. -- Jason Hunt Advanced Computer Systems You can use word mail merge to directly link to the database. No need to run a csv file. mt "Jason Hunt" <jhunt@advcs.ca> wrote in mess...

Outlook delegate-mailbox deletion problem
This sounds like an easy one however, haven't found resolution yet. Here's scenario... User B reported to User A and added A to his delegation for being copied on all meeting request replies (done from Outlook 2000). All was well until User A left the company and mailbox was subsequently archived and deleted. Ever since the mailbox deletion, all attendees accepting a meeting request from User B receive an NDR referencing inability to deliver reply to User A's mailbox. The typical answer would be to open User B's Outlook app, goto delegates and remove User A.. The problem is...

DLookup problems 06-17-07
Hi, I'm having some problems with the Dlookup function. I am trying to link a text box field from another table to a combo box that is linked to that other table. The combo box control source is: BuyerID This is a foreign key in the products table. the row source of the combo box(cboBuyerScreenName) is: SELECT [Buyers].[Buyer_ID], [Buyers].[Buyer_Screen_name] FROM Buyers; The text box control source: =DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" & [cboBuyerScreenName.buyerID]) The combo box does track the correct Buyer Screen Name but the...

Conditional Format #3
I'm sure this must be simple but I can't quite fathom it out. I want a conditional format that puts a colour in a cell if the cell is empty. Any ideas would be great Thanks Shona Shona, In the Conditional Formatting dialog, choose the 'equal to' option and enter ="" , the choose your format options. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "Shona" <Shona.Smith@uk.bosch.com> wrote in message news:biva1h$4q3$1@ns2.fe.internet.bosch.com... > I'm sure this must be simple but I can't quite ...

Cheque format
Hi, I use GP 9 and I have just changed the cheque date to comply with the Canadian Payments Association format of YYY MM DD using the information from the Microsoft knowledgebase. The font type and size in the body of the cheque has changed and I am now getting dingbats (**) before Dollar and cents and more dingbats (**) where the total amount of the cheque used to be in numerical form. This is what I would like to fix: 1) Font back to Arial size 9 or 10 2) no dingbats on cheque 3) date in correct format Thanks. -- Janice Hi Janice, I have a document that explains how to do this m...

How do I prevent the wrong entry in a field with multiple conditio
I have a form with a sequence of the same blank fields, which the user fills from another form control. The idea is that they select what they consider is the correct address, and the control sends the data to the next available empty field on the Target Form. All works well; except for when the user selects the incorrect entry, the action still takes place, and the incorect entry is placed in the empty field, and due to the nature of the action, the sequence moves to the next available empty field. I need a way of dealing with the incorrect entry. What I want to happen is nothing....

V3c desktop client problem
Hi All Hope someone can help? Have recently upgraded to outlook 2007 from 2003 on XP SP2. I uninstalled the old outlook client and installed the v3c client. Since then I the CRM menu and icons in outlook are greyed out and this error message appeared in the event viewer. Event Type: Warning Event Source: MSCRMAddin Event Category: None Event ID: 5904 Date: 15/03/2007 Time: 12:21:24 PM User: N/A Computer: ACER Description: The Microsoft CRM Outlook add-in was not initialized because Microsoft Outlook was started under a profile for which Microsoft CRM was not installed. Restart Mi...

Subform Problem 12-28-07
Hello, I have an Access form that contains a subform. the subform is connected to an access table. I need to have VBA (or I could use a query) to update the access table. However, the data shown in the form do not update. checking error messages in VBA, I discovered that the VBA could not write to the table because it is locked by the subform that is in the form. How can I work around this? Is it possible to unlock the table so the VBA can update it? Thank you, Keith On Dec 28, 9:52 am, Keith <Ke...@discussions.microsoft.com> wrote: > Hello, > I have an Access form th...

Lost Formatting When tab is copied
My problem would seem to be very basic. I have a table that has values with dollars in columns and then a rate (i.e. one cell less another and then divide) in percentage format. When I copy an existing tab either into the same workbook or into a new workbook, many of my percent formats change to dollar format. This happens no matter what page I copy and it happens on pages that I am not even touching or copying. Often times these cells that lose their formatting are next to cells that are in dollar format that are pulling their data from pivot table. Could this have anything to do with it? ...

outlook mail problem #2
i am running windows xp professional when i try to send a email a error comes up that says: does not recognize one or more names i recive mail find . can anyone help thanks gt gregg trenor <gt@irsauction.com> wrote: > i am running windows xp professional when i try to send > a email a error comes up that says: > does not recognize one or more names > i recive mail find . can anyone help thanks gt Please report, stating the exact text of the error messane, you Outlook version, and the type of account you're using. This isn't alt.psychic.read.my.mind -- Brian T...

Formating 2005345 to only 34!
Hello I got this date 2005345 which means year 2005, week 34, day 5 I want excel to format these values 2005345 and only show W34 How is this possible? /Anders -- a94andwi ------------------------------------------------------------------------ a94andwi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21077 View this thread: http://www.excelforum.com/showthread.php?threadid=401138 With data in A1 try ="W"&MID(A1,5,2) -- Regards Roger Govier "a94andwi" <a94andwi.1uoqyc_1125587105.3224@excelforum-nospam.com> wrote in message...

Converting PST from (97-2002) format to (2003) format?
Hi Gurus, Is there any way to convert existing PST files from (97- 2002) format to (2003) format? One way would be to create a PST file in 2003 format and copy all items from old PST file into new one and then start using new PST file.... .....But was wondering if there is any easy way? Not copy, but import. That's the easiest method. Note that importing will break all connects between contacts and items linked to them and may also break distribution lists in the new file. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, P...

Subform problem
I have a main form called [frm private all] that contains a command button that opens another (smaller) form called [frm private companies]. The smaller form contains data that is related to the main form (the main form contains data about encounters with clients, the smaller form contains the client info - e.g. address, phone numbers etc.). New clients are added by clicking the button to open the smaller form, adding the new client, then closing the smaller form: the OnClose event of the smaller form is [Forms]![frm private all]![Combo68company].Requery - [Combo68company] contains the client ...

Date formating bug
How do you stop excel from automatically turning information in a range (like 8-12) to a date (August 12). This makes me so angry I can spit nails because what should be a quick cut and paste becomes hours of extra work re-entering the ranges excel automatically changes to dates with a leading ' to make it text. Someone else has to have noticed this bug. Jim It's not a 'bug' it is a feature, 'by design'. It's great when you want it, but a real pain when you don't. You can however pre-format the range as text (Format>Cells>Number>Text) and ...