Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting for time
I have a value in cell E4 which is in hh:mm format. If a value located in cells F4:T4 are less than E4, I would like that value to appear as BLACK fill and white font. If the value of F4:T4 is equal to or greater than E4, I would like it to be white fill and black font. Any suggestions? Thanks!
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting for time
On Mon, 27 Jan 2014 06:06:39 -0800 (PST), Lee Radney wrote:
I have a value in cell E4 which is in hh:mm format. If a value located in cells F4:T4 are less than E4, I would like that value to appear as BLACK fill and white font. If the value of F4:T4 is equal to or greater than E4, I would like it to be white fill and black font. Any suggestions? Thanks! Take a look at Conditional Formatting |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting for time
"Lee Radney" wrote:
I have a value in cell E4 which is in hh:mm format. The format (appearance) does not matter as long it is truly Excel time. That is, ISNUMBER(E4) returns TRUE. However, many people mistakenly (or on purpose) include the date in E4, even if it is formatted to display only time. Often, this is because "experts" suggest formulas such as =NOW() instead of =--TEXT(NOW(),"hh:mm"), for example. Also, sometimes the actual time value is not the __exactly__ the same (internal representation) as the displayed time value. Differences arise because you have used =MOD(NOW(),1), an oft-suggested alternative to =--TEXT(NOW(),"hh:mm"), or otherwise the time value with more precision; or because you have computed time, for example =E5+TIME(0,5,0). "Lee Radney" wrote: If a value located in cells F4:T4 are less than E4, I would like that value to appear as BLACK fill and white font. If the value of F4:T4 is equal to or greater than E4, I would like it to be white fill and black font. Any suggestions? Since you neglect to mention the version of Excel that you use, it is difficult to offer click-by-click by instructions. I prefer to eschew the fance CF feature introduced in Excel 2007. Instead, I always enter a CF formula, as we always did in Excel 2003. One way to enter the CF formula is to select F4:T4, then to enter the following formula: =--TEXT(F4,"hh:mm")<--TEXT($E$4,"hh:mm") then click on Format and select the desired fill (pattern) and font colors. Note the careful use of relative references (F4) and absolute references ($E$4). Excel will change F4 to G4, H4, etc, but it will not change $E$4. The form =--TEXT(...)<--TEXT(...) guards against some of the mistakes and anomalies in the paragraphs above. __Sometimes__ the following will suffice: =F4<$E$4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting with time | Excel Discussion (Misc queries) | |||
HELP with conditional formatting and a max time | Excel Worksheet Functions | |||
conditional formatting time | Excel Discussion (Misc queries) | |||
Conditional formatting and time | Excel Worksheet Functions | |||
Conditional Formatting on Time | Excel Programming |