Conditional Formatting Not Working As Intended

I have the following Conditional Formatting rules set up.  This is the order 
they appear in the Conditional Formatting Rules Manager:

Rule                               Format     Applies to
Cell Value > "$H$41"       L.Blue       =$E$3:$J$3
Cell Value = "$H$41"       D.Green    =$E$3:$J$3
Cell Value >= "$G$41"     L.Green    =$E$3:$J$3
Cell Value >= "$F$41"     Yellow      =$E$3:$J$3
Cell Value < "$F$41"        Red         =$E$3:$J$3

Cell Value is set to =$E$41.  The problem is the affected cells are Red no 
matter what value $E$41 is (i.e. < F41, >= F41, >= G41, = H41 or > H41).  The 
values of E41, F41, G41 and H41 change with each new entry and the formatting 
SHOULD change accordingly.  I tried changing the order but that didn't work 
either.  How do I make these rules apply as intended?
0
Utf
2/5/2010 5:26:06 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
631 Views

Similar Articles

[PageSpeed] 0

Bishop wrote:
> I have the following Conditional Formatting rules set up.  This is the order 
> they appear in the Conditional Formatting Rules Manager:
> 
> Rule                               Format     Applies to
> Cell Value > "$H$41"       L.Blue       =$E$3:$J$3
> Cell Value = "$H$41"       D.Green    =$E$3:$J$3
> Cell Value >= "$G$41"     L.Green    =$E$3:$J$3
> Cell Value >= "$F$41"     Yellow      =$E$3:$J$3
> Cell Value < "$F$41"        Red         =$E$3:$J$3
> 
> Cell Value is set to =$E$41.  The problem is the affected cells are Red no 
> matter what value $E$41 is (i.e. < F41, >= F41, >= G41, = H41 or > H41).  The 
> values of E41, F41, G41 and H41 change with each new entry and the formatting 
> SHOULD change accordingly.  I tried changing the order but that didn't work 
> either.  How do I make these rules apply as intended?


Maybe get rid of the quotes?
0
Glenn
2/5/2010 5:30:39 PM
That's how the information is presented in the Conditional Formatting Rules 
Manager.

"Glenn" wrote:

> Bishop wrote:
> > I have the following Conditional Formatting rules set up.  This is the order 
> > they appear in the Conditional Formatting Rules Manager:
> > 
> > Rule                               Format     Applies to
> > Cell Value > "$H$41"       L.Blue       =$E$3:$J$3
> > Cell Value = "$H$41"       D.Green    =$E$3:$J$3
> > Cell Value >= "$G$41"     L.Green    =$E$3:$J$3
> > Cell Value >= "$F$41"     Yellow      =$E$3:$J$3
> > Cell Value < "$F$41"        Red         =$E$3:$J$3
> > 
> > Cell Value is set to =$E$41.  The problem is the affected cells are Red no 
> > matter what value $E$41 is (i.e. < F41, >= F41, >= G41, = H41 or > H41).  The 
> > values of E41, F41, G41 and H41 change with each new entry and the formatting 
> > SHOULD change accordingly.  I tried changing the order but that didn't work 
> > either.  How do I make these rules apply as intended?
> 
> 
> Maybe get rid of the quotes?
> .
> 
0
Utf
2/5/2010 8:18:18 PM
I see what you mean now.  The formatter actually added the quotes when I set 
up the conditions.  I went and manually removed them and it works now.  Thank 
you!

"Glenn" wrote:

> Bishop wrote:
> > I have the following Conditional Formatting rules set up.  This is the order 
> > they appear in the Conditional Formatting Rules Manager:
> > 
> > Rule                               Format     Applies to
> > Cell Value > "$H$41"       L.Blue       =$E$3:$J$3
> > Cell Value = "$H$41"       D.Green    =$E$3:$J$3
> > Cell Value >= "$G$41"     L.Green    =$E$3:$J$3
> > Cell Value >= "$F$41"     Yellow      =$E$3:$J$3
> > Cell Value < "$F$41"        Red         =$E$3:$J$3
> > 
> > Cell Value is set to =$E$41.  The problem is the affected cells are Red no 
> > matter what value $E$41 is (i.e. < F41, >= F41, >= G41, = H41 or > H41).  The 
> > values of E41, F41, G41 and H41 change with each new entry and the formatting 
> > SHOULD change accordingly.  I tried changing the order but that didn't work 
> > either.  How do I make these rules apply as intended?
> 
> 
> Maybe get rid of the quotes?
> .
> 
0
Utf
2/5/2010 8:51:01 PM
Reply:

Similar Artilces:

Export to excel from ASP.Net
I have a page wherein the contents of a datagrid are exported to an excel file. All the columns are correctly displayed in excel except for the number columns. The formatting string for the number column is {0:#,##0.00; (#,##0.00);0.00}. The data is formatted for all numbers exceeding 1000 but for any number below 1000 the data is displayed as 1000 and not with 0's in the decimal places. (displayed as 999 instead of 999.00). ...

Shortcut to format painter
Is ther a keyboard shortcut to the format painter and if not what is the code line i should use to create my own shortcut plaese Judith : A few links on keyboard shortcuts, et al. Chip Pearson http://www.cpearson.com/excel/ShortCuts.htm David McRitchie http://www.mvps.org/dmcritchie/excel/shortx2k.htm I've never seen a shortcut for the Format Painter. David McRitchie's site has a link to a macro that provides some functionality. Search the page for 'Painter'. HTH Paul ------------------------------------------------------------------------------------------------------...

sending attachments in rich text format
This is a multi-part message in MIME format. ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit Can anybody tell me how I send an attachment with an e-mail from within Microsoft Office 2007 in rich text format please Wendy ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: image/gif; name="QMARK.GIF" Content-Transfer-Encoding: base64 Content-ID: <7D394D5C2EB043D9A973E27399E2B9B5@andyPC> R0lGODlhEwAfAPcAAP8ICP8PDv8VFf8dHP8kI/...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Attachments convert to text format
When I send a message with an attachment in Outlook XP some receivers claim that all they receive is a text mail. My default mail format is plain text. If I change it to HTML I fear some recivers would not see the content at all. How can I solve it ? Note: I change the format of a particular mail if it contains an attachment, but sometimes I miss to do that. Tork2001 wrote: > When I send a message with an attachment in Outlook XP some receivers claim > that all they receive is a text mail. > My default mail format is plain text. If I change it to HTML I fear some > recivers ...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

2007 Slide Numbering doesn't work
I have a presentation in which the numbering doesn't work...I've gone step by step through everything I can find but I don't see it. Checked the master to ensure a placeholder was there, that the text is visible. I click on the slide thumbnail, click insert / slide number, check off slide numbering, click apply to all and okay. Nothing appears. When I go back to insert / slide number, nothing is checked. Any thoughts? Try ticking the option for slide number in Insert | Header and Footer instead. -- Echo [MS PPT MVP] http://www.echosvoice.com What's ne...

Figures locked in text format
I imported data from a web page and used the feature that allows me to post the info to Excel with the destination formatting of the file I am posting it to. However, when I try to manipulate the data, as I have done in the past with other data, it will not let me reformat the text to number- no matter what I do to it. I've tried everything! I want to be able to sum the column, etc. Give us an example of the data you are working with. And what have you tried to "do to it"? Regards, Fred "Marcia" <Marcia@discussions.microsoft.com> wrote in ...

Excel limited cell formats
I have run into Excel's limit on 4000 cell foremats. I have since split the excel workbook into two separate workbooks. My question is, how can I launch these from a template and have the two workbooks link with each other, My original workbook has 25 separate sheets within the workbook for a total of 3.25mb size. I have a whole slew of macros assigned to different command buttons. Mike Johnson ...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

downloaded service pack 1 and now Microsoft Excel isn't working ri
When I got to the icon for the save option I get this message "The file ' .xls' may have been changed by another user since you last saved it. In that case, what do you want to do?" *save a copy *overwrite changes this dialoge box is getting annoying, If I choose save a copy I have copy's of my files all over the place, if I overwrite then it does just that. But why do I have to do this everytime I open a file? I have looked everywhere and can't find my answer. Please remember that I just downloaded the Windows update service pack 1 and I believe that this i...

IIf statement sometimes works
I have the following IIf statement on an unbound field on a form: =IIf([Loaned/Out]="0"," ","Out of Service - Please Exit and select another Vehicle") Sometimes it comes back with the correct "Out of Service..." or nothing if the vehicle is not designated as loaned/out, but other times it comes back with "#NAME?". Why is it not consistent? Thanks. -- Donna N. 1. If [loaned/out] is ever null you will get #name#. 2. Is [loaned/out] ever not equal to "0"? Is "0" text or a number? If it is a number, you ...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Query Not Working 06-11-07
Hello, I have set up a query for 1 table. The field I'm trying to run the query on is a text field. I enter a name (="smith, john") I get zero results. However I have confirmed that this user exists in the table. Also (if this helps), this field gets populated via a combo box on a form. The table being used for the lookup contains a "user" field which is also a text field Thanks. sometimes this is caused by a leading blank in the field. It displays very narrow. one solution sname: TRIM([field]) "CT" <ctroy01@hotmail.com> wrote in message ...

XSLT, XPath, and XSL Formatting Objects (XSLFO)
does dot net support apis for XSLFO in addition to XSLT? Thanks In data Tue, 13 Jul 2004 11:47:40 -0500, David Laub ha scritto: > does dot net support apis for XSLFO in addition to XSLT? AFAIK no, you have to use nfop: http://nfop.sourceforge.net/ -- Lawrence "In IE we trust" Lawrence Oluyede wrote: >>does dot net support apis for XSLFO in addition to XSLT? > > > AFAIK no, you have to use nfop: > http://nfop.sourceforge.net/ You can, but you don't have to. There are plenty of production quality XSL-FO formatters supporting .NET. Most of them ar...

conditional running sum
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields: Date Adbn% 01/01/09 (Mon) 5% 01/02/09 (Tues) 6% 01/03/09 (Wed) 9% 01/04/09 (Thurs) 6% 01/05/09 (Fri) 8% I need it to end up with the following: Date Abdn% 01/01/09 5% 01/02/09 5.5% Avg of Mon&Tues 01/03/09 6.6% Avg Mon...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

ShowDependents Method works inconsistently?
Goal: To show all dependents of a range of cells. Problem: The code (see below) works sporatically and is therefore useless. For some groups of cells it will work perfectly, showing all dependents. In other cases, it will show only the dependents of the initially active cell. Will someone smarter than me please tell me what the heck is going on?!? (If it matters, I am using Excel 2002.) Sub Showem_all() ' Show all dependencies for selected region of cells. x = ActiveCell.CurrentRegion.Columns.Count y = ActiveCell.CurrentRegion.Rows.Count For i = 1 To x For j = 1 To...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

Cell formatting: displaying lat/long coordinates
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Rather than formatting a cell to display time in hours and minutes and = seconds, I would like to display latitude and longitude in degrees, = minutes, and seconds. e.g. 43=BA 25' 34" Is this possible in Excel 2000? ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W...

Excel 2002
When I press the left / right or up/down arrow keys in Excel 2002 (Vista) it scrolls the spreadsheet columns or rows, it does not move the cursor form cell to cell. How can I restore the normal cell to cell capability? Thanks Hi Bob Press your Scroll Lock key on the top right HTH John "+Bob+" <nomailplease@example.com> wrote in message news:62hnd5pmurr5fh1cltn1t3ie537pcjb479@4ax.com... > When I press the left / right or up/down arrow keys in Excel 2002 > (Vista) it scrolls the spreadsheet columns or rows, it does not move > the cursor form cell to cell. > > ...

I'm looking for a cookbook page format
Final copy will be bound in 6 x 9 inch format (book size). Thanks :) 2 recipes per page I'm thinking. ...

excel command that counts conditions met in 2 columns?
is there a form of countif that increments only if conditions are met in two (or more) columns? e.g., countif(colA = 1 and colB = 2) Hi You need SUMPRODUCT. Have a look here for some guidance and post back if you need some more help. http://www.contextures.com/xlFunctions01.html#SumProduct Hope this helps. Andy. "brendalw" <brendalw@discussions.microsoft.com> wrote in message news:F21CD5F8-8679-4A8E-90FC-AA35657C19DC@microsoft.com... > is there a form of countif that increments only if conditions are met in > two > (or more) columns? e.g., countif(colA = 1 a...

More formatting
I print karaoke books with the artist in column 1 and the song titles in column2.. Is there a way to move each artists song titles below the artists name in column 1? col1 col2 To This Col1 Artist Song1 Artist Song2 Song1 Song3 Song2 Song3 -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29417 View this thread: http://www.excelforum...