Columns I and J on my sheet are date columns. I need the font to be blue if
the date is greater than today. The dates will be both typed and copied in.

Can conditional format be used, or would font colur go back to normal as
time passes?

Gareth

Gareth,

Conditional formatting can do what you want. But as you say, it will revert
as time passes (but as it's not greater than today at some point, isn't that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to be blue
if
the date is greater than today. The dates will be both typed and copied
in.

Can conditional format be used, or would font colur go back to normal as
time passes?

Gareth

Gareth,

Are you saying that when you copy something into column I, the colour is
that of the copied data (even if you had previously made I blue)?

I guess that you could try worksheet event code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
Target.EntireColumn.Font.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

I don't want the font color to change from blue when the date in I or J
has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point, isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to be
blue
if
the date is greater than today. The dates will be both typed and
copied
in.

Can conditional format be used, or would font colur go back to normal
as
time passes?

Gareth

The data is copied in black (by macro), I want to highlight any dates that
are in the future. Similarly, any dates that are typed in I want any in the
future to be blue and any <= today to be black.

Your code needs amending to take account of the " today" part. Could you
do the honours?

Many thanks.

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Are you saying that when you copy something into column I, the colour is
that of the copied data (even if you had previously made I blue)?

I guess that you could try worksheet event code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
Target.EntireColumn.Font.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

I don't want the font color to change from blue when the date in I or J
has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point,
isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to be
blue
if
the date is greater than today. The dates will be both typed and
copied
in.

Can conditional format be used, or would font colur go back to
normal
as
time passes?

Gareth

Gareth,

Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
If Target.Value Date Then
Target.Font.ColorIndex = 5
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
The data is copied in black (by macro), I want to highlight any dates that
are in the future. Similarly, any dates that are typed in I want any in
the
future to be blue and any <= today to be black.

Your code needs amending to take account of the " today" part. Could you
do the honours?

Many thanks.

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Are you saying that when you copy something into column I, the colour is
that of the copied data (even if you had previously made I blue)?

I guess that you could try worksheet event code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
Target.EntireColumn.Font.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

I don't want the font color to change from blue when the date in I or
J
has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point,
isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to
be
blue
if
the date is greater than today. The dates will be both typed and
copied
in.

Can conditional format be used, or would font colur go back to
normal
as
time passes?

Gareth

Bob

Works great for dates typed in but doesn't work for any dates that are
copied into the I or J, another scenario is that dates are 'shifted' into
the range (Insert | Cells | Shift cells right) It doesn't work when this
happens either.

Any way around this?

"Bob Phillips" wrote in message
...
Gareth,

Are you saying that when you copy something into column I, the colour is
that of the copied data (even if you had previously made I blue)?

I guess that you could try worksheet event code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
Target.EntireColumn.Font.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

I don't want the font color to change from blue when the date in I or J
has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point,
isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to be
blue
if
the date is greater than today. The dates will be both typed and
copied
in.

Can conditional format be used, or would font colur go back to
normal
as
time passes?

Gareth

Gareth,

I am surprised that it doesn't work when the data is copied in, as it does
in my tests. I'm not surprised the cells shift doesn't work though, as there
is no change happening to trigger the event.

The only sure way I can think of is to have a macro that you trigger when
you are happy that all is done. This would just cycle through all of the
data and update the colour. For example

Public Sub ColourDates()
Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count, "I").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "I").Value Date Then
Cells(i, "I").Font.ColorIndex = 5
End If
Next i

cLastRow = Cells(Rows.Count, "J").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "J").Value Date Then
Cells(i, "J").Font.ColorIndex = 5
End If
Next i

End Sub

You could link this to a toolbar button.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

Works great for dates typed in but doesn't work for any dates that are
copied into the I or J, another scenario is that dates are 'shifted' into
the range (Insert | Cells | Shift cells right) It doesn't work when this
happens either.

Any way around this?

"Bob Phillips" wrote in message
...
Gareth,

Are you saying that when you copy something into column I, the colour is
that of the copied data (even if you had previously made I blue)?

I guess that you could try worksheet event code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 9 Or Target.Column = 10 Then
Target.EntireColumn.Font.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Bob

I don't want the font color to change from blue when the date in I or
J
has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point,
isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to
be
blue
if
the date is greater than today. The dates will be both typed and
copied
in.

Can conditional format be used, or would font colur go back to
normal
as
time passes?

Gareth

Bob

I don't want the font color to change from blue when the date in I or J has
passed. That is why I thought CF might not be suitable.

Would some kind of sheet event work?

Gareth

"Bob Phillips" wrote in message
...
Gareth,

Conditional formatting can do what you want. But as you say, it will
revert
as time passes (but as it's not greater than today at some point, isn't
that
appropriate?).

Just use a formula like, =I1TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Columns I and J on my sheet are date columns. I need the font to be
blue
if
the date is greater than today. The dates will be both typed and copied
in.

Can conditional format be used, or would font colur go back to normal as
time passes?

Gareth