Conditional Formatting

C

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
 
B

Barb Reinhardt

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

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
 
B

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.
 
C

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
 
D

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.
Click to expand...

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.
 
B

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.
 
C

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
 
V

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
 
C

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
 
G

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
 
C

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
 
Top