CF for specific ranges of time for an entire column

[C.Poppell]

I'm trying to do conditional formatting for an entire column of times, to
show the time in red if it is outside of a range of time.

Column D, E and F all contain times (without dates), and I need to show the
time in column D and E red if they are greater than 75 minutes and 45 minutes
of the time in column F respectively. The formatting I've been using is as
follows:

Cell Value > F4-(TIME(0,75,0)) [result is colored red if it is greater than
the time]
and
Cell Value > F4-(TIME(0,45,0)) [same as above]

But I have to insert the formatting in each specific cell, which gets very
tedious for the amount of times I need to do it...
Please tell me there is a way to conditional format an entire colum to
compare (for example) cell 5 in column D to cell 5 in column F and show the
time in cell 5 red if the difference is greater than a specific time.

 

[C.Poppell]

hrm, I totally butchered that... and it doesn't seem like I can edit my
original post. So the formula I'm using actually does what I want, which is
turn the time red if it is -less- than 75 or 45 minutes, not greater than...
so just swap less than for any greater than's you see and you'll get what I
actually want.

 

[JMay]

If you have successfully created a c/f in Column D (Say row 2), why not just
use the "FORMAT-PAINT" Button on your toolbar to apply your D2 to D3100?
Same for Column E (E2 to E3:E100)

hrm, I totally butchered that... and it doesn't seem like I can edit my
original post. So the formula I'm using actually does what I want, which is
turn the time red if it is -less- than 75 or 45 minutes, not greater than...
so just swap less than for any greater than's you see and you'll get what I
actually want.

I'm trying to do conditional formatting for an entire column of times, to
show the time in red if it is outside of a range of time.

Column D, E and F all contain times (without dates), and I need to show the
time in column D and E red if they are greater than 75 minutes and 45 minutes
of the time in column F respectively. The formatting I've been using is as
follows:

Cell Value > F4-(TIME(0,75,0)) [result is colored red if it is greater than
the time]
and
Cell Value > F4-(TIME(0,45,0)) [same as above]

But I have to insert the formatting in each specific cell, which gets very
tedious for the amount of times I need to do it...
Please tell me there is a way to conditional format an entire colum to
compare (for example) cell 5 in column D to cell 5 in column F and show the
time in cell 5 red if the difference is greater than a specific time.

 

[C.Poppell]

Unfortunately that doesn't update the c/f for each field, it just copies it,
so I would be comparing D30 to F2, when I want to compare it to F30.

If you have successfully created a c/f in Column D (Say row 2), why not just
use the "FORMAT-PAINT" Button on your toolbar to apply your D2 to D3100?
Same for Column E (E2 to E3:E100)

hrm, I totally butchered that... and it doesn't seem like I can edit my
original post. So the formula I'm using actually does what I want, which is
turn the time red if it is -less- than 75 or 45 minutes, not greater than...
so just swap less than for any greater than's you see and you'll get what I
actually want.

I'm trying to do conditional formatting for an entire column of times, to
show the time in red if it is outside of a range of time.

Column D, E and F all contain times (without dates), and I need to show the
time in column D and E red if they are greater than 75 minutes and 45 minutes
of the time in column F respectively. The formatting I've been using is as
follows:

Cell Value > F4-(TIME(0,75,0)) [result is colored red if it is greater than
the time]
and
Cell Value > F4-(TIME(0,45,0)) [same as above]

But I have to insert the formatting in each specific cell, which gets very
tedious for the amount of times I need to do it...
Please tell me there is a way to conditional format an entire colum to
compare (for example) cell 5 in column D to cell 5 in column F and show the
time in cell 5 red if the difference is greater than a specific time.

 

[David Biddulph]

Not true.
If your orginal comparison in row 2 was with F2, it would change to F30 when
you copied it down to row 30. It would compare with F2 if you had put F$2
rather than F2.
--
David Biddulph

Unfortunately that doesn't update the c/f for each field, it just copies
it,
so I would be comparing D30 to F2, when I want to compare it to F30.

If you have successfully created a c/f in Column D (Say row 2), why not
just
use the "FORMAT-PAINT" Button on your toolbar to apply your D2 to
D3100?
Same for Column E (E2 to E3:E100)

hrm, I totally butchered that... and it doesn't seem like I can edit my
original post. So the formula I'm using actually does what I want,
which is
turn the time red if it is -less- than 75 or 45 minutes, not greater
than...
so just swap less than for any greater than's you see and you'll get
what I
actually want.

:

I'm trying to do conditional formatting for an entire column of
times, to
show the time in red if it is outside of a range of time.

Column D, E and F all contain times (without dates), and I need to
show the
time in column D and E red if they are greater than 75 minutes and 45
minutes
of the time in column F respectively. The formatting I've been using
is as
follows:

Cell Value > F4-(TIME(0,75,0)) [result is colored red if it is
greater than
the time]
and
Cell Value > F4-(TIME(0,45,0)) [same as above]

But I have to insert the formatting in each specific cell, which gets
very
tedious for the amount of times I need to do it...
Please tell me there is a way to conditional format an entire colum
to
compare (for example) cell 5 in column D to cell 5 in column F and
show the
time in cell 5 red if the difference is greater than a specific time.

 

[C.Poppell]

I actually tried it before I replied and gave the result I got... using the
format painter I copied the formating in D2 and 'painted' it into D330, the
result was every cell from D330 comparing to F2.

 

[David Biddulph]

Have you looked at what the CF condition showed in row 30?
Copy the text from that condition into the newsgroup so that we can see it;
don't try to retype.

 

[C.Poppell]

Yes. This is a copy/paste of what the formula looks like after I format
paint it to the rest of the lines.
Oddly, the quotations are not in the original formula.
="F4-(time(0,75,0))"

As a side note, I'm going home now, I'll be able to look in on this
tommorrow and try out all of the wonderful suggestions... hopefully I don't
have to input 30X3X15 formulas anytime someone hoses them up again.

 

[David Biddulph]

There's your problem. If you are seeing the quotation marks, take them out,
but leave the = sign there.
If you type in a string in the CF condition without preceding it by an =
sign, Excel assumes that it is a text string and puts the quote marks around
it.
--
David Biddulph

Yes. This is a copy/paste of what the formula looks like after I format
paint it to the rest of the lines.
Oddly, the quotations are not in the original formula.
="F4-(time(0,75,0))"

As a side note, I'm going home now, I'll be able to look in on this
tommorrow and try out all of the wonderful suggestions... hopefully I
don't
have to input 30X3X15 formulas anytime someone hoses them up again.

 

[C.Poppell]

You're so awesome! Thankyou Thankyou Thankyou... I don't have to type about
a thousand formulas out now.

My co-workers were complaining about all these extra quotation marks, but
now I get to tell them it was his fault