CF for specific ranges of time for an entire column
 

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.

CF for specific ranges of time for an entire column
 

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.

"C.Poppell" wrote:

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.

CF for specific ranges of time for an entire column
 

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 D3:D100?
Same for Column E (E2 to E3:E100)

"C.Poppell" wrote:

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.

"C.Poppell" wrote:

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.

CF for specific ranges of time for an entire column
 

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.

"JMay" wrote:

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 D3:D100?
Same for Column E (E2 to E3:E100)

"C.Poppell" wrote:

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.

"C.Poppell" wrote:

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.

CF for specific ranges of time for an entire column
 

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

"C.Poppell" wrote in message
...
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.

"JMay" wrote:

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
D3:D100?
Same for Column E (E2 to E3:E100)

"C.Poppell" wrote:

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.

"C.Poppell" wrote:

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.

CF for specific ranges of time for an entire column
 

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 D3:D30, the
result was every cell from D3:D30 comparing to F2.

"David Biddulph" wrote:

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

CF for specific ranges of time for an entire column
 

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.
--
David Biddulph


"C.Poppell" wrote in message
...
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 D3:D30,
the
result was every cell from D3:D30 comparing to F2.

"David Biddulph" wrote:

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

CF for specific ranges of time for an entire column
 

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" wrote:

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.
--
David Biddulph


"C.Poppell" wrote in message
...
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 D3:D30,
the
result was every cell from D3:D30 comparing to F2.

"David Biddulph" wrote:

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

CF for specific ranges of time for an entire column
 

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

"C.Poppell" wrote in message
...
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" wrote:

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.
--
David Biddulph


"C.Poppell" wrote in message
...
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
D3:D30,
the
result was every cell from D3:D30 comparing to F2.

"David Biddulph" wrote:

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

CF for specific ranges of time for an entire column
 

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 :)


"David Biddulph" wrote:

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

"C.Poppell" wrote in message
...
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" wrote:

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.
--
David Biddulph


"C.Poppell" wrote in message
...
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
D3:D30,
the
result was every cell from D3:D30 comparing to F2.

"David Biddulph" wrote:

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