excel 2002 question ( dates )
15-06-2005, 12:16
|
#1
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
excel 2002 question ( dates )
|
|
|
15-06-2005, 12:26
|
#2
|
|
Inactive
Join Date: Jun 2005
Location: Peterborough
Posts: 5,106
|
Re: excel 2002 question ( dates )
=IF(TODAY()<C2,1,2)
where C2 holds the date
1 = true action
2 = false action
|
|
|
15-06-2005, 12:30
|
#3
|
|
Inactive
Join Date: Jun 2003
Location: Belfast
Age: 46
Posts: 4,594
|
Re: excel 2002 question ( dates )
Quote:
|
Originally Posted by paulyoung666
|
I actually did something similar a while ago. It would loop through a data range and change the cell to red if the date had passed or green if the date was still in the future. Here is the code..
Code:
For Each c In Worksheets("Section 1").Range("c4:q35")
intdifference = ""
If c.Value = "" Then
c.Interior.Color = RGB(255, 255, 255)
Else
tdate = c.Value
intdifference = DateDiff("d", Now, tdate)
If intdifference < 0 Then
c.Interior.Color = RGB(255, 0, 0)
ElseIf intdifference > 0 Then
c.Interior.Color = RGB(0, 255, 0)
Else
c.Interior.Color = RGB(255, 255, 255)
End If
End If
Next c
|
|
|
15-06-2005, 12:43
|
#4
|
|
Inactive
Join Date: Jun 2003
Location: Yorks
Age: 59
Services: VM TV package. VM phone and 200MB internet & slow Tivo
Posts: 2,332
|
Re: excel 2002 question ( dates )
Quote:
|
Originally Posted by Wicked_and_Crazy
=IF(TODAY()<C2,1,2)
where C2 holds the date
1 = true action
2 = false action
|
That works well if you use conditional formatting....just tried it.
|
|
|
15-06-2005, 13:29
|
#5
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
|
|
|
15-06-2005, 18:43
|
#6
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
for the terminally thick , can someone point me in the right direction please
|
|
|
15-06-2005, 18:48
|
#7
|
|
Inactive
Join Date: Feb 2004
Location: There's no place like 127.0.0.1
Services: Depends on the person and the price they're offering
Posts: 12,384
|
Re: excel 2002 question ( dates )
Conditional Formatting:
Format > Conditional Formatting (from the menu bar).
__________________
Although I can't get conditional formatting to do it either, so I must be terminally thick!
If someone's managed to get this to work could they upload a spreadsheet please so that we can see an example?
|
|
|
15-06-2005, 19:15
|
#8
|
|
Inactive
Join Date: Jun 2003
Location: Belfast
Age: 46
Posts: 4,594
|
Re: excel 2002 question ( dates )
The code above is pretty simple to use. Go into the Visual Basic Editor (Alt+11) select this workbook and then select workbook and open from the drop down menu.
All you need to do is change the this line... For Each c In Worksheets("Section 1").Range("c4:q35") to your sheet name and range.
This will run every time you open the spreadsheet and change dates in the past to red and dates in the future to green.
|
|
|
15-06-2005, 19:36
|
#9
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
Quote:
|
Originally Posted by Raistlin
Conditional Formatting:
Format > Conditional Formatting (from the menu bar).
__________________
Although I can't get conditional formatting to do it either, so I must be terminally thick!
If someone's managed to get this to work could they upload a spreadsheet please so that we can see an example?
|
now thats an idea , i really want to get this to work
|
|
|
15-06-2005, 19:42
|
#10
|
|
Inactive
Join Date: Feb 2004
Location: There's no place like 127.0.0.1
Services: Depends on the person and the price they're offering
Posts: 12,384
|
Re: excel 2002 question ( dates )
Here ya go, using the code gazzae gave above.
Watch the dates change colour as you open the sheet.
Now save it.
Now change your system date back a year.
Now open it again.
|
|
|
16-06-2005, 22:25
|
#11
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
|
|
|
16-06-2005, 22:32
|
#12
|
|
Inactive
Join Date: Feb 2004
Location: There's no place like 127.0.0.1
Services: Depends on the person and the price they're offering
Posts: 12,384
|
Re: excel 2002 question ( dates )
The grid lines are still there, it's something in the cell formatting that is causing you to lose sight of them.
I'll take another look now I'm home and see if I can find it for you.
|
|
|
16-06-2005, 22:33
|
#13
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
Quote:
|
Originally Posted by Raistlin
The grid lines are still there, it's something in the cell formatting that is causing you to lose sight of them.
I'll take another look now I'm home and see if I can find it for you.
|
kinda what i thought, could it be that they are being overwritten in white ????
|
|
|
16-06-2005, 22:37
|
#14
|
|
Inactive
Join Date: Feb 2004
Location: There's no place like 127.0.0.1
Services: Depends on the person and the price they're offering
Posts: 12,384
|
Re: excel 2002 question ( dates )
You still about? I've got an interesting twist for you.....
Want me to email you with it?
|
|
|
16-06-2005, 22:39
|
#15
|
|
Permanently Banned
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
|
Re: excel 2002 question ( dates )
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT. The time now is 06:22.
|