Conditional Formatting in Excel 2007

[neillstoddart]

I have a column of cells which are formatted as either General or
hh:mm.
What I want is - if A1 = Off & A2 is less than 06:00 then A1 is
highlighted in red text.
Haven't found anything that works yet.
Can anyone help?
Thanks

 

[Mike H]

Try this conditional format formula in A1

=AND(A1="Off",ISNUMBER(A2),A2<TIME(6,0,0))

Mike

 

[neillstoddart]

Thanks Mike,
Now how do I apply that formula to a whole rang of cells within that
column.
Sorry if these are simple questions, but I didn't seem to get any
sense from the built in help!

Neill

 

[Bob Phillips]

Select all the cells and just use the formula as if you were only applying
to the first. Excel will adjust them all.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

I don't really understand what you are trying to do but if you want a
selection of cells to check A1 & A2 then do this. Select those cells then
apply this slightly modified conditional format

=AND($A$1="Off",ISNUMBER($A$2),$A$2<TIME(6,0,0))

Mike

 

[neillstoddart]

The columns will get very long and will contain either a time or
"Off".
If the cell below "Off" is after 06:00 there's no problem, If it's
before 06:00 then "Off" needs highlighting.
The first formula you gave works great (I've changed the cell
references to C1068 & C1069 to fit in with my table), but when I
select all the cells I want it to apply to, then either "edit rule" or
"new rule" & paste the formula in, it doesn't seem to work.
Thanks again

 

[daddylonglegs]

As Bob says, you can select the whole range first and then apply Mike's
original formula adjusted for your cell refs, so......

If your range is C1068:C2000 select that whole range and use

=AND(C1068="Off",ISNUMBER(C1069),C1069<TIME(6,0,0))