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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>
>>
>>