| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
"Barb Reinhardt" wrote in message
... "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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 -- changetires ------------------------------------------------------------------------ changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414 View this thread: http://www.excelforum.com/showthread...hreadid=564803 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Hi, Select H36 and go to conditional formatting. Enter this formula =IF($H36G36,TRUE,FALSE) To deduct time change the format of cell T51 to [H]:MM. That should work VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=564803 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 -- changetires ------------------------------------------------------------------------ changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414 View this thread: http://www.excelforum.com/showthread...hreadid=564803 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
Can you post an example of data -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=564803 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formatting
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 -- changetires ------------------------------------------------------------------------ changetires's Profile: http://www.excelforum.com/member.php...o&userid=35414 View this thread: http://www.excelforum.com/showthread...hreadid=564803 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting a range with conditional formatting | Excel Worksheet Functions | |||
| conditional formatting glitches | Excel Discussion (Misc queries) | |||
| Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
| conditional formatting | Excel Discussion (Misc queries) | |||
| cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions | |||
Linear Mode
