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

 

[Guest]

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

 

[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

 

[Guest]

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]

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

E

 

[David Biddulph]

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.

 

[Guest]

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]

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 cel
that are hidden still have an effect on the conditional formatting.
Whats even worse is there are some other cells that are working fin
and do not have those pesky small values messing with the conditiona
formatting and I cannot figure out why some cells have the really smal
decimal values and other cells do not. Why?

E

 

[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

 

[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 fi
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 i
0.0208333333357587
the actual excel machine language format for cell N69 i
0.0208333333333333

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

E