1. I need a my cells containing dates to highlight red when the current date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to a
different one (basically I want to do a series). I've even tried using the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no $
in the formula to be removed so it will do a series (I read this in someone
else's post). How can I do this?

Hi, some of this may not be what you asked, but what have.. I do not use the
format for cell id that you use, but with work did on dates / formatting
different examples.

Incidently, are you not doing a copy-paste special, formats?
c.f.'s for date passing # days (in reference cell)

=IF(OR(AX9="",BT9="",BT9=0,$AX$4="z"),"",OR((TODAY ()+1)(AX9+AX$5),AND(AX9<"",OR(AY9="",AZ9=""))))

=IF(AND(AY9="",AZ9="",OR(BT9="",BT9=0,$AX$4="z")), "",(TODAY()+1)(AX9+AX$6))

=IF(OR(BT9="",BT9=0,$AX$4="x",$AX$4="z"),"",(TODAY ()+1)(AX9+AX$7))
------------------------------
formula for hand entering a date in another column as: :yymmdd or ;yymmdd
=IF(OR(BT9={"",0}),"",IF(OR(G9="br",AND(LEN(S9)=5 ,MID(S9,5,1)="q")),"b",IF(LEFT(U9,2)=":c","c",IF(A ND(BT9<1,OR(LEFT(U9,1)={":",";"}),ISNUMBER(ABS(MID (U9,2,6)))),
IF(TODAY()DATE(MID(U9,2,2)+100,MID(U9,4,2),MID(U9 ,6,2)-(10+1)),"d",IF(LEFT(U9,1)=";","-","")),IF(RIGHT(CB9,1)="x","z",IF(BT9<1,"?","")))) ))

----------------------
c.f.'s used for above:
=IF(AND(OR(LEFT(U9,1)=":",LEFT(U9,1)=";"),ISNUMBER (ABS(MID(U9,2,6)))),AND(BT9<1,TODAY()DATE(MID(U9, 2,2)+100,MID(U9,4,2),MID(U9,6,2)-3)),AND(LEN(S9)=5,MID(S9,5,1)="q"))

=IF(DX9="","",OR(G9="br",H9="br",DX9="w:",LEFT(U9, 5)=":call",LEFT(U9,5)=";call",IF(AND(OR(LEFT(U9,1) =":",LEFT(U9,1)=";"),ISNUMBER(ABS(MID(U9,2,6)))),T ODAY()DATE(MID(U9,2,2)+100,MID(U9,4,2),MID(U9,6,2 )-(5+1)))))


=IF(OR(BT9="",BT9=0,AND(BT9=1,R9<"c"),LEFT(U9,3) =":ok"),"",OR(R9="c",U9="",LEFT(U9,2)=":?",IF(BT9< 1,NOT(AND(LEFT(U9,1)=":",ISNUMBER(ABS(MID(U9,2,6)) )))),IF(ISNUMBER(ABS(MID(U9,2,6))),TODAY()DATE(MI D(U9,2,2)+100,MID(U9,4,2),MID(U9,6,2)-(10+1)))))

XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Will" wrote:

1. I need a my cells containing dates to highlight red when the current date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to a
different one (basically I want to do a series). I've even tried using the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no $
in the formula to be removed so it will do a series (I read this in someone
else's post). How can I do this?

p.s.: I guesse the code used to get the date, using for first c.f.'s would
help.
got from someone on this site.. modified

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Target.Row < 80 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
If Not Intersect(Me.Range("AY:AZ"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "AX")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub



"Will" wrote:

1. I need a my cells containing dates to highlight red when the current date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to a
different one (basically I want to do a series). I've even tried using the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no $
in the formula to be removed so it will do a series (I read this in someone
else's post). How can I do this?

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the current
date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to
a
different one (basically I want to do a series). I've even tried using
the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no
$
in the formula to be removed so it will do a series (I read this in
someone
else's post). How can I do this?

@_@ Wow.....please tell me there's an easier formula or even better if
anyone knows a good link I can use to look up the information myself. (The
Excel help menu gave me nothing).

As for the Paste Special function, it didn't work for me. I'm probably just
missing something so if anyone has any other ideas, or links, I would
appreciate it.

Thanks for your info nastech! :)

"nastech" wrote:

Hi, some of this may not be what you asked, but what have.. I do not use the
format for cell id that you use, but with work did on dates / formatting
different examples.

Incidently, are you not doing a copy-paste special, formats?
c.f.'s for date passing # days (in reference cell)

=IF(OR(AX9="",BT9="",BT9=0,$AX$4="z"),"",OR((TODAY ()+1)(AX9+AX$5),AND(AX9<"",OR(AY9="",AZ9=""))))

=IF(AND(AY9="",AZ9="",OR(BT9="",BT9=0,$AX$4="z")), "",(TODAY()+1)(AX9+AX$6))

=IF(OR(BT9="",BT9=0,$AX$4="x",$AX$4="z"),"",(TODAY ()+1)(AX9+AX$7))
------------------------------
formula for hand entering a date in another column as: :yymmdd or ;yymmdd
=IF(OR(BT9={"",0}),"",IF(OR(G9="br",AND(LEN(S9)=5 ,MID(S9,5,1)="q")),"b",IF(LEFT(U9,2)=":c","c",IF(A ND(BT9<1,OR(LEFT(U9,1)={":",";"}),ISNUMBER(ABS(MID (U9,2,6)))),
IF(TODAY()DATE(MID(U9,2,2)+100,MID(U9,4,2),MID(U9 ,6,2)-(10+1)),"d",IF(LEFT(U9,1)=";","-","")),IF(RIGHT(CB9,1)="x","z",IF(BT9<1,"?","")))) ))

----------------------
c.f.'s used for above:
=IF(AND(OR(LEFT(U9,1)=":",LEFT(U9,1)=";"),ISNUMBER (ABS(MID(U9,2,6)))),AND(BT9<1,TODAY()DATE(MID(U9, 2,2)+100,MID(U9,4,2),MID(U9,6,2)-3)),AND(LEN(S9)=5,MID(S9,5,1)="q"))

=IF(DX9="","",OR(G9="br",H9="br",DX9="w:",LEFT(U9, 5)=":call",LEFT(U9,5)=";call",IF(AND(OR(LEFT(U9,1) =":",LEFT(U9,1)=";"),ISNUMBER(ABS(MID(U9,2,6)))),T ODAY()DATE(MID(U9,2,2)+100,MID(U9,4,2),MID(U9,6,2 )-(5+1)))))


=IF(OR(BT9="",BT9=0,AND(BT9=1,R9<"c"),LEFT(U9,3) =":ok"),"",OR(R9="c",U9="",LEFT(U9,2)=":?",IF(BT9< 1,NOT(AND(LEFT(U9,1)=":",ISNUMBER(ABS(MID(U9,2,6)) )))),IF(ISNUMBER(ABS(MID(U9,2,6))),TODAY()DATE(MI D(U9,2,2)+100,MID(U9,4,2),MID(U9,6,2)-(10+1)))))

XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"Will" wrote:

1. I need a my cells containing dates to highlight red when the current date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to a
different one (basically I want to do a series). I've even tried using the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no $
in the formula to be removed so it will do a series (I read this in someone
else's post). How can I do this?

Perfect!! Now, when the current date is the same as the date entered into
the cell it turns clear so what's the sign for greater than or equal to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the current
date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to
a
different one (basically I want to do a series). I've even tried using
the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no
$
in the formula to be removed so it will do a series (I read this in
someone
else's post). How can I do this?

= ... sorry, wasn't sure you didn't know how to do conditional formats...
just woke up while ago :o

"Will" wrote:

Perfect!! Now, when the current date is the same as the date entered into
the cell it turns clear so what's the sign for greater than or equal to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the current
date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to
a
different one (basically I want to do a series). I've even tried using
the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no
$
in the formula to be removed so it will do a series (I read this in
someone
else's post). How can I do this?

Lol, that's ok. That's what I needed, thanks! Works great. Now I just need
to figure out how to copy them to different cells as a series instead of a
straight copy. It's been a pain redoing all of them one at a time. :)

"nastech" wrote:

= ... sorry, wasn't sure you didn't know how to do conditional formats...
just woke up while ago :o

"Will" wrote:

Perfect!! Now, when the current date is the same as the date entered into
the cell it turns clear so what's the sign for greater than or equal to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the current
date
passes the date within the cell (basically I need to know when things are
behind).

2. How do I copy a conditional format from one cell and have it apply to
a
different one (basically I want to do a series). I've even tried using
the
special paste tool and selected formating and it still applies to the
original cell and not the new one(ie: format 1 =R5C7 and when copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7). There are no
$
in the formula to be removed so it will do a series (I read this in
someone
else's post). How can I do this?

If you did as I suggested, there is no need to copy, they are all set in a
block action.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
Lol, that's ok. That's what I needed, thanks! Works great. Now I just
need
to figure out how to copy them to different cells as a series instead of a
straight copy. It's been a pain redoing all of them one at a time. :)

"nastech" wrote:

= ... sorry, wasn't sure you didn't know how to do conditional
formats...
just woke up while ago :o

"Will" wrote:

Perfect!! Now, when the current date is the same as the date entered
into
the cell it turns clear so what's the sign for greater than or equal
to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the
current
date
passes the date within the cell (basically I need to know when
things are
behind).

2. How do I copy a conditional format from one cell and have it
apply to
a
different one (basically I want to do a series). I've even tried
using
the
special paste tool and selected formating and it still applies to
the
original cell and not the new one(ie: format 1 =R5C7 and when
copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7).
There are no
$
in the formula to be removed so it will do a series (I read this
in
someone
else's post). How can I do this?

I entered the formula just as you had it and it worked great. It did exactly
what needed it to do, now I need to get it to my other rows. I have 100's of
rows I need to do the same thing with. It doesn't automatically put my
conditional formats into other cells without copying (unless I'm missing
something somewhere). So unless you meant something else or that I need to
create a macro to do it, it's not working like that for me. When you copy a
regular formula from one row to another, it references the cells within that
row that you copied to, not the row it was copied from.
I work in Excel often enough but not often with conditional formating. More
explaination would be helpfull on that front because it's not just this
conditional format that I want copied.

"Bob Phillips" wrote:

If you did as I suggested, there is no need to copy, they are all set in a
block action.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
Lol, that's ok. That's what I needed, thanks! Works great. Now I just
need
to figure out how to copy them to different cells as a series instead of a
straight copy. It's been a pain redoing all of them one at a time. :)

"nastech" wrote:

= ... sorry, wasn't sure you didn't know how to do conditional
formats...
just woke up while ago :o

"Will" wrote:

Perfect!! Now, when the current date is the same as the date entered
into
the cell it turns clear so what's the sign for greater than or equal
to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when the
current
date
passes the date within the cell (basically I need to know when
things are
behind).

2. How do I copy a conditional format from one cell and have it
apply to
a
different one (basically I want to do a series). I've even tried
using
the
special paste tool and selected formating and it still applies to
the
original cell and not the new one(ie: format 1 =R5C7 and when
copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7).
There are no
$
in the formula to be removed so it will do a series (I read this
in
someone
else's post). How can I do this?

Hi, for just copying to different rows below, do a copy, paste-special,
formats. it you don't know how to do that, reply will explain.. but for
going to different columns you need to figure out each cell reference, if you
want to keep it static or not (i.e. fixed, excel calls it: absolute
reference) you just put a $ (dollar sign) in front of anything you don't
want to change when moving (with copy-paste special..)

e.g.: keep same column use: $a1
copy down, and/or left/right (formats or formula's) will always refer back
to just the "a" column, but will change for every row down you copy "down".

$a$1 will always refer to just one cell: a1

"Will" wrote:

I entered the formula just as you had it and it worked great. It did exactly
what needed it to do, now I need to get it to my other rows. I have 100's of
rows I need to do the same thing with. It doesn't automatically put my
conditional formats into other cells without copying (unless I'm missing
something somewhere). So unless you meant something else or that I need to
create a macro to do it, it's not working like that for me. When you copy a
regular formula from one row to another, it references the cells within that
row that you copied to, not the row it was copied from.
I work in Excel often enough but not often with conditional formating. More
explaination would be helpfull on that front because it's not just this
conditional format that I want copied.

"Bob Phillips" wrote:

You are not listening. I said ... Select all of the target cells, let's
assume the first is B3

By doing so the CF is applied to all of those target cells in one action.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
I entered the formula just as you had it and it worked great. It did
exactly
what needed it to do, now I need to get it to my other rows. I have 100's
of
rows I need to do the same thing with. It doesn't automatically put my
conditional formats into other cells without copying (unless I'm missing
something somewhere). So unless you meant something else or that I need
to
create a macro to do it, it's not working like that for me. When you copy
a
regular formula from one row to another, it references the cells within
that
row that you copied to, not the row it was copied from.
I work in Excel often enough but not often with conditional formating.
More
explaination would be helpfull on that front because it's not just this
conditional format that I want copied.

"Bob Phillips" wrote:

If you did as I suggested, there is no need to copy, they are all set in
a
block action.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Will" wrote in message
...
Lol, that's ok. That's what I needed, thanks! Works great. Now I
just
need
to figure out how to copy them to different cells as a series instead
of a
straight copy. It's been a pain redoing all of them one at a time. :)

"nastech" wrote:

= ... sorry, wasn't sure you didn't know how to do conditional
formats...
just woke up while ago :o

"Will" wrote:

Perfect!! Now, when the current date is the same as the date
entered
into
the cell it turns clear so what's the sign for greater than or
equal
to in
formula speak?

"Bob Phillips" wrote:

Select all of the target cells, let's assume the first is B3

Goto CF, FormatConditional Formatting

Change Condition1 to Formula Is

Add a formula of =B3<TODAY()

Select Pattern tab

Select a colour

OK out

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Will" wrote in message
...
1. I need a my cells containing dates to highlight red when
the
current
date
passes the date within the cell (basically I need to know when
things are
behind).

2. How do I copy a conditional format from one cell and have
it
apply to
a
different one (basically I want to do a series). I've even
tried
using
the
special paste tool and selected formating and it still applies
to
the
original cell and not the new one(ie: format 1 =R5C7 and when
copied to
row
7, column 7 it will still format to =R5C7 instead of =R7C7).
There are no
$
in the formula to be removed so it will do a series (I read
this
in
someone
else's post). How can I do this?

Ok, and it will only let me select 1 "source" cell. For example: In column 3
rows 6-15, I want the color to change to red when the current date has
elapsed the dates entered in column 8 row 6-15. So, looking at my last
posts, let me be clearer. The cells containing the dates (source) that I
wish to use, are not the ones that I want to change color when the current
date has elapsed them. So basically, I'm referencing one group of cells in
one column to look at another group of cells in a different column.

Doing it your way, my rows in column 3 are only referring to the 1st row
selected as the source (row 6) in column 8, meaning, they all turn color when
a date is entered into R6C8 but not when entered into say R7C8 or R8C8. They
are only recognizing info in R6C8.

I did not explain this well in above posts, so I hope it's clearer now what
I am needing and if not, well, I'll just have to fidle with it.

Nastec, paste-special format doesn't work (it still keeps the absolute cell
ref). For example: formula in cell R3C10 is =R3C10<R3C7 (green), copy R3C10
then paste-special format to R7C10. Formula stays =R3C10<R3C7 (green)
instead of changing to =R7C10<R7C7 (green). I'm copying within the same
column to different rows and when I try entering any of these $, it gets
angry and says the formula is wrong. So, if you were me, using this exact
formula in my example, show me what the formula would look like with these $
where they should go to get the copy to work.

Thanks for being patient through my explanations. I'm not explaining myself
very well but you guys have helped me to get my report to run smoother.

Will,

When entering in the CF formula, make sure that the cell reference does
not have the $ signs. Then you can either Paste Special Formats or once
the first cell is set, click on the Format Painter (looks like a paint
brush) and highlight your other cells. By removing the $ signs (which
is the default if you select the cells when applying the CF formula) it
will change the cell references accordingly.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560821

Never mind. My excel was defaulted to the R1C1 style cell ref instead of the
A1 cell ref. The copy formula works great now! Thanks!!

"Will" wrote:

Ok, and it will only let me select 1 "source" cell. For example: In column 3
rows 6-15, I want the color to change to red when the current date has
elapsed the dates entered in column 8 row 6-15. So, looking at my last
posts, let me be clearer. The cells containing the dates (source) that I
wish to use, are not the ones that I want to change color when the current
date has elapsed them. So basically, I'm referencing one group of cells in
one column to look at another group of cells in a different column.

Doing it your way, my rows in column 3 are only referring to the 1st row
selected as the source (row 6) in column 8, meaning, they all turn color when
a date is entered into R6C8 but not when entered into say R7C8 or R8C8. They
are only recognizing info in R6C8.

I did not explain this well in above posts, so I hope it's clearer now what
I am needing and if not, well, I'll just have to fidle with it.

Nastec, paste-special format doesn't work (it still keeps the absolute cell
ref). For example: formula in cell R3C10 is =R3C10<R3C7 (green), copy R3C10
then paste-special format to R7C10. Formula stays =R3C10<R3C7 (green)
instead of changing to =R7C10<R7C7 (green). I'm copying within the same
column to different rows and when I try entering any of these $, it gets
angry and says the formula is wrong. So, if you were me, using this exact
formula in my example, show me what the formula would look like with these $
where they should go to get the copy to work.

Thanks for being patient through my explanations. I'm not explaining myself
very well but you guys have helped me to get my report to run smoother.

Will,

Bob's method is easier but again just be sure that your CF formula cell
references do not contain the $ signs.

Sorry Bob, didn't read all the posts.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=560821