Lee Radney said:
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 said:
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
