Conditional Formatting

[changetires]

Why does my cell turn red when the conditions are either not met. Example, Cell t51 conditions are (turn the cell red when cell h36 is greater than G36. If my answer in cell h36 is equal to g36 the cell is still turning red although you can clearly see that they are just equal.

Whats the problem?


ED

[Barb Reinhardt]

Can you post the formula for the condition? Also, is it possible that H36 =
3.000001 and G36 = 3.00000?

"changetires" wrote:

>
> Why does my cell turn red when the conditions are either not met.
> Example, Cell t51 conditions are (turn the cell red when cell h36 is
> greater than G36. If my answer in cell h36 is equal to g36 the cell is
> still turning red although you can clearly see that they are just
> equal.
>
> Whats the problem?
>
>
> ED
>
>
> --
> changetires
> ------------------------------------------------------------------------
> changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414
> View this thread: http://www.excelforum.com/showthread...hreadid=564803
>
>

[changetires]

the answers are in HH:MM format when the times are equal then they will display the color red. Some cells work fine the the conditions but some cells will show red


Ed

[Barb Reinhardt]

Try this: In a couple of other cells in the workbook, assign them to be the
values in G3 and H3 (if I remember them correctly). Then format these cells
as NUMBER and change the number of decimal places until you see if there is
really a difference.

"changetires" wrote:

>
> the answers are in HH:MM format when the times are equal then they will
> display the color red. Some cells work fine the the conditions but some
> cells will show red
>
>
> Ed
>
>
> --
> changetires
> ------------------------------------------------------------------------
> changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414
> View this thread: http://www.excelforum.com/showthread...hreadid=564803
>
>

[changetires]

Thats it, about 25 decimals out the number is larger but if they are formatted the same and everything why would it have a larger number even if it is only just a small amount? How can I correct this problem?

Ed

[David Biddulph]

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> "changetires" wrote:
>
>>
>> the answers are in HH:MM format when the times are equal then they will
>> display the color red. Some cells work fine the the conditions but some
>> cells will show red

> Try this: In a couple of other cells in the workbook, assign them to be
> the
> values in G3 and H3 (if I remember them correctly). Then format these
> cells
> as NUMBER and change the number of decimal places until you see if there
> is
> really a difference.

Or put =G36-H36 in a cell and see whether it is really zero.

If this is your problem, then set your conditional format to test for
(ABS(G3-H3)<0.00001) or some appropriate figure depending how close you want
to be.
--
David Biddulph

[Barb Reinhardt]

I've seen it before, but I'm not sure why it happens. You may want to post
another question on the board specific to that problem. I'm sure someone out
there knows the answer.

"changetires" wrote:

>
> Thats it, about 25 decimals out the number is larger but if they are
> formatted the same and everything why would it have a larger number
> even if it is only just a small amount? How can I correct this
> problem?
>
> Ed
>
>
> --
> changetires
> ------------------------------------------------------------------------
> changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414
> View this thread: http://www.excelforum.com/showthread...hreadid=564803
>
>

[changetires]

Thanks that works. But why is it when both cells are in HH:MM format and you subtract the two they create this number like 0.00283533333333333? That is just a PITA and now you have to go back and reinvent the wheel with your conditional formatting. Is there anyway around this?

Ed

[VBA Noob]

Hi,

Select H36 and go to conditional formatting. Enter this formula

=IF($H36>G36,TRUE,FALSE)

To deduct time change the format of cell T51 to [H]:MM. That should work

VBA Noob

[changetires]

That formula works VBA but the really small values assigned to the cell that are hidden still have an effect on the conditional formatting. Whats even worse is there are some other cells that are working fine and do not have those pesky small values messing with the conditional formatting and I cannot figure out why some cells have the really small decimal values and other cells do not. Why?

Ed

[Gord Dibben]

Excel stores dates and times as numbers.

With a day being 1, an hour is 1/24th, a minute is 1/1440th and a second is

1/86440th

When you start adding, subtracting these times you get decimal numbers as you
describe.

See Chip Pearson's site for rounding times.

http://www.cpearson.com/excel/datearith.htm

Scroll down to near bottom of page.


Gord Dibben MS Excel MVP

On Tue, 25 Jul 2006 15:08:56 -0400, changetires
<[email protected]> wrote:

>
>Thanks that works. But why is it when both cells are in HH:MM format
>and you subtract the two they create this number like
>0.00283533333333333? That is just a PITA and now you have to go back
>and reinvent the wheel with your conditional formatting. Is there
>anyway around this?
>
>Ed

[VBA Noob]

Can you post an example of data

[changetires]

Here is an example, I know why its turning my cell red but how can I fix that decimal problem with conditional formatting?

Data Cell M69 is formatted to read 00:30 Minutes
N69 is formatted to read 0:30 Minutes

Conditional Format in cell M69 is
Cell value is greater than N69 then red

ok

the acutal excel machine language format for cell M69 is 0.0208333333357587
the actual excel machine language format for cell N69 is 0.0208333333333333

I have changed the formats for them to look the same and that didnt cure anything. I mean its meeting the conditions to change it red but how can I write in an override or something?

Ed