Conditional Formatting Not Working Consistently

One of our Excel users is applying conditional formatting on cells within a 
worksheet that are linked to other worksheets in the same file and he is 
getting inconsistent results i.e., sometimes it applies the formatting and 
other times it won't. He seems to have the problem primarily when he selects 
a range within a column and then applies the conditional formatting.

The conditional formatting statement applied to cell C5 is:
Formula is =$B$5-$C$5=0 then format as red and bold.

He is concerned about the ability to rely on this feature working properly. 
Since he is our Accounting Manager, he would like to use this feature 
extensively to produce a variety of management reports.

Any suggestions?
-- 
Christina
0
Christina (58)
7/22/2005 8:27:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
707 Views

Similar Articles

[PageSpeed] 53

This formula creates a circular reference as a regular formula, so I
wouldn't expect it to work for a CF formula either.
*******************
~Anne Troy

www.OfficeArticles.com


"Christina" <Christina@discussions.microsoft.com> wrote in message
news:68882DD8-A1DF-406D-B6D9-E7D5A6D0DFD3@microsoft.com...
> One of our Excel users is applying conditional formatting on cells within
a
> worksheet that are linked to other worksheets in the same file and he is
> getting inconsistent results i.e., sometimes it applies the formatting and
> other times it won't. He seems to have the problem primarily when he
selects
> a range within a column and then applies the conditional formatting.
>
> The conditional formatting statement applied to cell C5 is:
> Formula is =$B$5-$C$5=0 then format as red and bold.
>
> He is concerned about the ability to rely on this feature working
properly.
> Since he is our Accounting Manager, he would like to use this feature
> extensively to produce a variety of management reports.
>
> Any suggestions?
> -- 
> Christina


0
ng1 (1444)
7/22/2005 8:32:18 PM
That's what I thought, too, when I looked at the formula. But it does work as 
I tried applying it to a single cell.
-- 
Christina


"Anne Troy" wrote:

> This formula creates a circular reference as a regular formula, so I
> wouldn't expect it to work for a CF formula either.
> *******************
> ~Anne Troy
> 
> www.OfficeArticles.com
> 
> 
> "Christina" <Christina@discussions.microsoft.com> wrote in message
> news:68882DD8-A1DF-406D-B6D9-E7D5A6D0DFD3@microsoft.com...
> > One of our Excel users is applying conditional formatting on cells within
> a
> > worksheet that are linked to other worksheets in the same file and he is
> > getting inconsistent results i.e., sometimes it applies the formatting and
> > other times it won't. He seems to have the problem primarily when he
> selects
> > a range within a column and then applies the conditional formatting.
> >
> > The conditional formatting statement applied to cell C5 is:
> > Formula is =$B$5-$C$5=0 then format as red and bold.
> >
> > He is concerned about the ability to rely on this feature working
> properly.
> > Since he is our Accounting Manager, he would like to use this feature
> > extensively to produce a variety of management reports.
> >
> > Any suggestions?
> > -- 
> > Christina
> 
> 
> 
0
Christina (58)
7/22/2005 8:44:02 PM
Anne,
The circular reference reference does not apply here I dont think. I tried 
this formula as it stands in the mail and it does work.
Regards,
Alan.

"Anne Troy" <ng@officearticles.com> wrote in message 
news:eCi90xvjFHA.3448@TK2MSFTNGP12.phx.gbl...
> This formula creates a circular reference as a regular formula, so I
> wouldn't expect it to work for a CF formula either.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "Christina" <Christina@discussions.microsoft.com> wrote in message
> news:68882DD8-A1DF-406D-B6D9-E7D5A6D0DFD3@microsoft.com...
>> One of our Excel users is applying conditional formatting on cells within
> a
>> worksheet that are linked to other worksheets in the same file and he is
>> getting inconsistent results i.e., sometimes it applies the formatting 
>> and
>> other times it won't. He seems to have the problem primarily when he
> selects
>> a range within a column and then applies the conditional formatting.
>>
>> The conditional formatting statement applied to cell C5 is:
>> Formula is =$B$5-$C$5=0 then format as red and bold.
>>
>> He is concerned about the ability to rely on this feature working
> properly.
>> Since he is our Accounting Manager, he would like to use this feature
>> extensively to produce a variety of management reports.
>>
>> Any suggestions?
>> -- 
>> Christina
>
> 


0
alan111 (581)
7/22/2005 8:46:48 PM
Hi Earl:

Funny, you would say that because when I asked him to show me what he was 
doing, it worked. Likely, he had just switched worksheets.

I will pass this along to him and see how it goes. Thanks.

By the way, the attachment didn't come through.
-- 
Christina


"Earl Kiosterud" wrote:

> Christina,
> 
> B5 may not exactly equal C5.  The binary/decimal conversions cause small 
> errors
> that make such a comparison fail.  Try this:
> 
>  =ROUND(($B$5-$C$5),14)=0
> 
> Another problem is this:  Conditional formatting sometimes just doesn'twork. 
> I don't care what the computer salesman told you.  It may apply the 
> formatting to some of the cells, but not all. Switch to another sheet and 
> back, and it will appear properly.  Sometimes it fails to remove the 
> conditional formatting from all the cells when the condition is no longer 
> met, but again will do so when you switch to another sheet, then back.  I've 
> observed this with Excel 2002 Service Pack 3.  You may be in this area. 
> I've had to write my own Worksheet_Change subs to do this instead of using 
> CF.  I didn't find anything about this in the knowledge base.  I've attached 
> a picture of a failure where columns B and C are conditionally formatted 
> with the formula above.  The attachment may not make it through Microsoft's 
> news server.
> --
> Earl Kiosterud
> www.smokeylake.com
> 
> "Christina" <Christina@discussions.microsoft.com> wrote in message
> news:68882DD8-A1DF-406D-B6D9-E7D5A6D0DFD3@microsoft.com...
> > One of our Excel users is applying conditional formatting on cells within
> > a
> > worksheet that are linked to other worksheets in the same file and he is
> > getting inconsistent results i.e., sometimes it applies the formatting and
> > other times it won't. He seems to have the problem primarily when he
> > selects
> > a range within a column and then applies the conditional formatting.
> >
> > The conditional formatting statement applied to cell C5 is:
> > Formula is =$B$5-$C$5=0 then format as red and bold.
> >
> > He is concerned about the ability to rely on this feature working
> > properly.
> > Since he is our Accounting Manager, he would like to use this feature
> > extensively to produce a variety of management reports.
> >
> > Any suggestions?
> > -- 
> > Christina
> 
> 
> 
> 
> 
0
Christina (58)
7/22/2005 10:17:01 PM
Christina,

Switching worksheets seems to make the CF display properly with the current 
CF conditions, but if the conditions change, it can fail again.

The picture I attached came through in the microsoft news group.  I'm 
connected directly, in Outlook Express.  You're using the web -- they 
probably didn't let it through.

Some while back several of us bandied this about in one of these newsgroups. 
It was never resolved, as I recall.  I'm hoping more folks will weigh in on 
this.  It seems an unresolved problem.
--
Earl Kiosterud
www.smokeylake.com

"Christina" <Christina@discussions.microsoft.com> wrote in message 
news:DC4F66AB-A977-4767-86B6-34B1B6A7FEF2@microsoft.com...
> Hi Earl:
>
> Funny, you would say that because when I asked him to show me what he was
> doing, it worked. Likely, he had just switched worksheets.
>
> I will pass this along to him and see how it goes. Thanks.
>
> By the way, the attachment didn't come through.
> -- 
> Christina
>
>
> "Earl Kiosterud" wrote:
>
>> Christina,
>>
>> B5 may not exactly equal C5.  The binary/decimal conversions cause small
>> errors
>> that make such a comparison fail.  Try this:
>>
>>  =ROUND(($B$5-$C$5),14)=0
>>
>> Another problem is this:  Conditional formatting sometimes just 
>> doesn'twork.
>> I don't care what the computer salesman told you.  It may apply the
>> formatting to some of the cells, but not all. Switch to another sheet and
>> back, and it will appear properly.  Sometimes it fails to remove the
>> conditional formatting from all the cells when the condition is no longer
>> met, but again will do so when you switch to another sheet, then back. 
>> I've
>> observed this with Excel 2002 Service Pack 3.  You may be in this area.
>> I've had to write my own Worksheet_Change subs to do this instead of 
>> using
>> CF.  I didn't find anything about this in the knowledge base.  I've 
>> attached
>> a picture of a failure where columns B and C are conditionally formatted
>> with the formula above.  The attachment may not make it through 
>> Microsoft's
>> news server.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "Christina" <Christina@discussions.microsoft.com> wrote in message
>> news:68882DD8-A1DF-406D-B6D9-E7D5A6D0DFD3@microsoft.com...
>> > One of our Excel users is applying conditional formatting on cells 
>> > within
>> > a
>> > worksheet that are linked to other worksheets in the same file and he 
>> > is
>> > getting inconsistent results i.e., sometimes it applies the formatting 
>> > and
>> > other times it won't. He seems to have the problem primarily when he
>> > selects
>> > a range within a column and then applies the conditional formatting.
>> >
>> > The conditional formatting statement applied to cell C5 is:
>> > Formula is =$B$5-$C$5=0 then format as red and bold.
>> >
>> > He is concerned about the ability to rely on this feature working
>> > properly.
>> > Since he is our Accounting Manager, he would like to use this feature
>> > extensively to produce a variety of management reports.
>> >
>> > Any suggestions?
>> > -- 
>> > Christina
>>
>>
>>
>>
>> 


0
someone798 (944)
7/22/2005 10:55:45 PM
Reply:

Similar Artilces:

Opiions on what is the best method/ format for storing Procedures and Functions developed over time
I have hundreds of DBs and thousands of Procedures and Functions that I've written over time. Right now they are stored in the original DBs. To reuse them, I have to remember which GD DB they were in. Does anyone have a system or concept (preferably Access based) for storing these in an easily retrieved fashion? Thanks Kevin Hi Kevin, This is an interesting idea. Here is what I came up with. The idea is to export the modules as code, parse the procedures and save them in a record. Arvin has some code that will export each module as a text file: h...

Adding conditions to sales reports
For accounting reasons, our parent company needs to know the amount of sales made to employees of our store. I thought I had this figured out with a SQL query (see below) but it turns out the numbers from my query are very different from the numbers returned to the Sales > Top Customers report. Is there a way to run the Top Customers report, but adding a condition (not in the filter option list) to include only rows where Customer.Employee = 1? I appreciate any tips. Here's my SQL, maybe there's something easily seen that I missed... SELECT c.LastName + ', ' + c.Firs...

CRM Field Formatting NTEXT and NVARCHAR won't "Automatically expan
In CRM 3.0, I have created a custom entity. I've tried 2 approaches to creating a Description field that fills the available space on the form. 1. Create a ntext(2000) attribute, expose on the form, edit the field properties, Formatting tab, and check the "Automatically expand to use available space." checkbox. 2. Create a nvarchar(2000) attribute (display as textarea), expose on the form, edit the field properties, Formatting tab, and check the "Automatically expand to use available space." checkbox. Either way, the field does not auto expand when the form is ...

How do i format this time ?
Hi, i have a column with times in this format, i.e.: 433 10433 190433 which respectively mean the follwoing times: 00 h: 04 min: 33 s 01 h: 04 min: 33 s 19 h: 04 min: 33 s how can i set the cells so they show the time in the latter format ? I welcome any suggestions. Regards, Victor =3DLEFT(TEXT(A1,"000000"),2)& " hrs : " & MID(TEXT(A1,"000000"),3,2)&" Min : "&RIGHT(A1,2)&" S" On Nov 18, 5:37=A0pm, VICTOR <victor.hera...@gmail.com> wrote: > Hi, > > i have a column with t...

But hyphenation DOES work!
I'm running Word from Office 2007 (with that darned 'Ribbon'!), but in the Compatibility Mode with a document created in much-more-friendly Word 2003. Within a few seconds after my document loads, I get a message saying that the auto-hyphenation feature isn't resident, and that I should run the installation program again. Well, it IS working just fine, thank you, automatically hyphenating my .doc as I work. Is this a concern or just a bug? This might be a sign that *part* of the text is formatted in a language that hasn't been installed. Select the whole ...

Custom error message code not working?
From http://www.databasedev.co.uk/custom-error-message.html I use: Private Sub Form_Error(DataErr As Integer, Response As Integer) 'If an error occurs because of missing data in a required field 'display our own custom error message Const conErrRequiredData = 3314 If DataErr = conErrRequiredData Then MsgBox ("Please ensure that you enter a First Name and Last Name") Response = acDataErrContinue Else 'Display a standard error message Response = acdatadisplay End If End Sub I tested it with the error code 3022 (double valu...

Money 2004 Date Format Displaying Incorrectly
A nitnoid problem, but one that bugs me all the same. I am running Money 2004 Premium on a Windows 2000 operating system. I have my computer date regional options for date format set to: Short Date = dd-MMM-yy (e.g. 18 FEB 04) Long Date dddd, dd MMMM, yyyy (e.g. Wednesday, 18 February 2004) MY PROBLEM: Yet, in my Money 2004 version the date format shows as follows in the date columns of all account registers: Short Date = 18-2-04 (instead of 18-FEB-04) Long Date = February 18, 2004 (instead of 18 February 2004) When I change the regional options for dates to other formats, it...

Loop all Sheets not working.
I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() ‘The following hides columns A,C to E, G to M, and O to AL. ‘It then sets the column width for Columns B, F and N as well as the ‘row height for row 1. ‘Lastly, columns B, F and N are selected and formatting done to ‘ensure that they are not the Tex...

countif not working
vba excel 07 Want to count all strings that are two characters long in a range. Using countif(range, "??") but it doesn't work. The strings are numbers so I think that might be screwing it up. Tried countif(range,"<100") but that didn't work either. John Hi John Look at theese examples: Sub CountIt() 'Numbers only Set MyRange = Range("A1:A100") MyCount = WorksheetFunction.CountIf(MyRange, "<100") _ - WorksheetFunction.CountIf(MyRange, "<10") Debug.Print MyCount End Sub Sub CountIt2() 'N...

Workflow process not working
Hello, I am trying to configure MS_CRM and I have created an email rule so when a new case is created, it is suppose to email the customer automatically with a case number. However, it is not working as needed. If I go to the Action tab and apply rule, then it will send the email. How can I make this work so it does it when the email is created? In the workflow manager I created the following: When case is created if Case.Case Number not nukk then E-mail Template:New Case Auto Reply end if Any thoughts and advice is much appreciated ...

Help Doesn't work Office XP Pro / Standard
Running XP Office Pro on XP Pro. Help windows for all office products does not work properly. Empty, gray dialog box pops up, no hyperlinks, no text. Any advice is welcome. (except "RELOAD OS", that's not advice, that's a nightmare) Have you tried running Detect and Repair from the Help menu of any of the Office products? Make sure you have your Office CD handy when you do this. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "R T...

Weeks worked should be stored by check and not by transaction
Weeks worked is useless to us as is. The weeks-worked should be stored per check, not per pay transaction. If a person receives 3 paycodes in a check (for example: regular,holiday,vacation) they will be overstated times 3 because of weeks being attached to pay transactions. There is no single pay code that all employees receive each check, so putting weeks on a constant pay code is not an answer either. Also, where we answer the "weeks" question on the payroll build, the documentation says this applies to salaried people but not to hourly people. Why would anyone want weeks wo...

conditional format
I tried to convert a simple form in 2002 format. My database is a 2000 file. In this form i apply conditional format to many objects. In Access 2000 i display all with my conditions, but in the new version (Access XP) when display my form the objects blink. I tried with a simple form with less objects and all is ok. Have i too many objects? Why? Sorry for my english, i write from Italy and i don't speak and write english wery well!!! Thanks miki ...

Working with external quoting systems
We have CRM 3.0, which is only used for contact management since we have developed other applications in MS Access 2003 for developing quotes and working with files from other software packages. The Access system fetches the customer information the the CRM database and that's just about all. We plan to migrate to CRM 4.0 and would like to keep the functionality of our rather complex quoting system, but have it more fully integrated so we can use all the opportunity management features and sales automation and reporting features. My guess is that we should try to re-write the quote ...

Need help with conditions Liz
I've a question........... If A1 = 2/17/2007 B1 = 2/22/2007 I've cell C1 with a formula to calculate the difference between those dates and based on the result which appears on C1 I've a condition in cell D1 which is if C1 is greater or equal to 0 then YES and if less than 0 then NO. But I need one more condition that is if c1 is has no value which is when the A1 and B1 is blank I need the D1 cell to appear blank to but should have the condition in place. PS: I've formatted the C column by unchecking the zero values under Tools/Options/View. Thanks Liz See your...

Setting default formats for color and comments
How do I set my color palette as a default so that I don't have to keep changing it in each workbook. And I do set the default formats for Comment texts so that I don't have to keep changing it in each comment. Thanks. Hi for your comments question have a look at http://www.contextures.com/xlcomments02.html#Default For your color question the following site may help you http://www.mvps.org/dmcritchie/excel/colors.htm -- Regards Frank Kabel Frankfurt, Germany LF wrote: > How do I set my color palette as a default so that I don't have to > keep changing it in each workbo...

conditional formatting, empty cells
Hi faced to the following problem: Let us asssume that i want to c fin all values in column A with range 3 to 9 and make them red. Everything goes fine when I select the whole column, go to th conditional formatting and use the system: if cell value is not betwee 0-9 make the cell red. The blank cells are not colored BUT: when I use the same system with values between 3-9, the empt cells are colored to red and system is not working. Can anyone explain why this system is working from 0 to eternity an not from 1 to eternity.. -- Message posted from http://www.ExcelForum.com Hi are these ce...

How do I format a strikethrough font in Publisher 2003 (SP2)?
It must be there!... But I cannot find it. I am talking about the "strikethrough" font format in Publicher 2003. I can see several types of underlines, shadow and enbossing, but not the striketrough format. Please help. THANKS! I don't believe strikethrough is available in Publisher. The best you can do is draw lines through the text. -- Don Vancouver, USA "Marco Margaritelli" <MarcoMargaritelli@discussions.microsoft.com> wrote in message news:BF6D2138-9601-4BD4-B34C-3CE2B1DA0C53@microsoft.com... > It must be there!... But I cannot find it. > I am...

URLs in html format
I regularly send and reply to messages from people who use HTML format. However, if I include a URL, even though it is blue and underlined, it is not live. Can you tell me what I need to do to make the URLs live in HTML message format? Many thanks, John ...

Format a cell as seconds
Is there a way to format cells to appear as seconds. I would like to type 7 and have it generate a 0:00:07. I know that I could use the TIME function for this, but I would need two cells for that. I want to format the cell I'm working in. Thanks! Hi have a look at http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany Lisa wrote: > Is there a way to format cells to appear as seconds. I > would like to type 7 and have it generate a 0:00:07. I > know that I could use the TIME function for this, but I > would need two cells for tha...

Excel XML Time format & arithmetic
Hi all, I have an Excel Xml file and I want to perform a Sum operation with time format cells. If I set [h]:mm format to the cells ¡, I can see the next right values: 8:25 8:30 25:25 42:20 (SUM) When I save the file as Excel XML 2003, I have this: <Styles> <Style ss:ID="s64"> <NumberFormat ss:Format="[h]:mm"/> </Style> </Styles> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s64"><Data ss:Type="DateTime">1899-12-31T08:25:00.000</Data></Ce...

Help w/Conditional Formatting Code
I have used up my conditional formatting options in Conditional Formatting. I am trying to add two more conditions via code and need help with syntax please. Thank you in advance for any help you can provide. Private Sub Form Current() If Me![FieldName] = 1 or 2 Then Me!PtName.BackColor = 33023 Else Me!PtName.BackColor = 0 End If ciao TotallyConfused wrote: > I have used up my conditional formatting options in Conditional > Formatting. I am trying to add two more conditions via code and need > help with syntax please. Thank you in advance for any ...

Formating numbering in Publisher
Hello I am trying to change some bullet poins into a numbered list. So I put my cursor at the start of the line of text, and press the numbered list icon. It turns it into a numbered list, which is what I want it to do (as I go to each line I have to start the numbering at the next number in sequence, but that is fine) The trick is though, sometimes the numbers are bold, sometimes the numbers are normal. How can I make them all bold or all normal (The text is all normal) Thanks so much!!! Bosley wrote: > Hello > I am trying to change some bullet poins into a numbered list. So I &...

Excel Web Query not working!
Hello there. I've been using the excel web query functionality at home and at the office for a while now to retrieve stock quotes from yahoo finance. recently however it stopped working on two of my 3 machines! More specifically its the queries that require one to be logged in that have stopped working. I get an error message saying that "The Internet site reports that a conenction was extablished but the Data is not available" After reading the topics here and at other places on the web i tried: 1) Rolling back using system restore (to get rid of any updates that couldve brok...

formula to add a figure to a total if conditions are.....
Hi, OK, obviously by the subject excel is not my strong point! ;-) What I would like please is the formula to do the following. If the total in cell A1 is under 750, add 80 to the figure and total it. Does that make sense? Thanks Tony hi, put this in B1.... =IF(A1<750,A1+80,A1) Regards FSt1 "Mr Offle" wrote: > Hi, > OK, obviously by the subject excel is not my strong point! ;-) > What I would like please is the formula to do the following. > > If the total in cell A1 is under 750, add 80 to the figure and total it. > > Does that make sense? > >...