Help with some sheet code

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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, =I1>TODAY() in CF.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
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
 
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)
 
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?
 
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)
 
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
 
Back
Top