Excel VBA - Turn font colour red

[ADW2005]

I'm fairly new to Excel VBA - I have a worksheet that has a column of dates. What I want to do is as follows;

If the value in the cell (formatted as date, 14-Jul-05) is greater than the system date then I want to turn the contents of the cell to red and in bold?
I can turn the contents of the cell red using VBA but I think it's the argument I'm struggling with...

 

[longhair]

ADW2005,
can you post some code?
regards,
longhair

 

[PHV]

Have you tried to play with conditional format ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ADW2005]

Here is the code I've got - unfortunatley nothing happens!!!

Private Sub Worksheet_Activate()
Dim Today As Date


If ActiveCell.Value < Today Then ActiveCell.Font.Color = RGB(255, 0, 0)

End

End Sub

 

[longhair]

ADW2005,
maybe this is too simple but... you said you want the date in the cell to be greater than the system date.
shouldn't the '<' be '>'?
regards,
longhair

 

[ADW2005]

Oh yeah sorry - I meant greater than the system date - changing the '>' to a '<' doesn't make any difference either...

 

[PHV]

The system date is Date() (or Now()).
Where is Today coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

ADW2005,
works fine for me

Sub test()
If ActiveCell.Value > Today Then ActiveCell.Font.Color = RGB(255, 0, 0)

End Sub

are you in the cell that you want to change when you open the spreadsheet?
regards,
longhair

 

[ADW2005]

To be honest I'm just using activecell to test my code - What I really want to do is check the whole column when the spreadsheet opens without selecting a cell...

 

[PHV]

So, again, have you tried menu Format -> Conditional format when your whole column is selected ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

ADW2005,
then you will have to loop through the cells. but thats later. make sure you are in a cell that you want to change before saving & closing the spreadsheet. reopen and see if it works.
regards,
longhair

 

[ADW2005]

I don't seem to be getting anywhere - I'musing Excel 97 if this makes any difference?

 

[xlbo]

well what code are you now running and how are you running it ??

ps - PHV's suggestion to use conditional formatting would be much easier...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[longhair]

ADW2005,
where do you assign a value to the var Today? also please note that 'Today' is a key word in vba. try the following:

dim myToday as date
myToday = date
If ActiveCell.Value > myToday Then ActiveCell.Font.Color = RGB(255, 0, 0)

rather than using Private Sub Worksheet_Activate()
put the above code in a macro called test.
create another macro as follows:

Sub Auto_Open()
    Application.Run "workbookname.xls!test"
End Sub

hth
regards,
longhair

 

[ADW2005]

Excellent - I'm getting there using Longhairs recommendations! Thanks to all who contributed as well - Couldn't get the conditiona formatting to work by the way...

I have to run the Macro on each inidivdual cell I click on? How can we now get the macro to check the whole worksheet on open?

 

[xlbo]

Have a look at LOOPS in VBA help - especially along the lines of

For each c in selection

or

For each c in Range("myRange")



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi ADW2005

I have to say PHV's suggestion to use conditional formatting would be much easier. ( yes Geoff, I know you already said that )

I have a query for you ADW2005; you said:

Couldn't get the conditional formatting to work by the way...

so, what did you try? I'd use something like =A2>TODAY() as the conditional formula. Now that really is simple, isn't it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ADW2005]

Tried as you suggested but all values in the column are change and not the relevant ones...