Forum Articles
  Welcome back Join CF
You are here You are here: Home | Forum | Labeling software

You are currently viewing our boards as a guest which gives you limited access to view most of the discussions, articles and other free features. By joining our Virgin Media community you will have full access to all discussions, be able to view and post threads, communicate privately with other members (PM), respond to polls, upload your own images/photos, and access many other special features. Registration is fast, simple and absolutely free so please join our community today.


Welcome to Cable Forum
Go Back   Cable Forum > Computers & IT > General IT Discussion
Register FAQ Community Calendar

excel 2002 question ( dates )
Reply
 
Thread Tools
Old 15-06-2005, 12:16   #1
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
excel 2002 question ( dates )

is it possible to have a cell in excel react to a calender , what i want is a spreadsheet that will change the font colour of a cell when a calender date is exceeded , i think that makes sense anyway

answers on a postcard please , and as usual , large greenies for helpful posts
paulyoung666 is offline   Reply With Quote
Advertisement
Old 15-06-2005, 12:26   #2
Wicked_and_Crazy
Inactive
 
Join Date: Jun 2005
Location: Peterborough
Posts: 5,106
Wicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny star
Wicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny starWicked_and_Crazy has a nice shiny star
Send a message via MSN to Wicked_and_Crazy Send a message via Yahoo to Wicked_and_Crazy
Re: excel 2002 question ( dates )

=IF(TODAY()<C2,1,2)

where C2 holds the date

1 = true action

2 = false action
Wicked_and_Crazy is offline   Reply With Quote
Old 15-06-2005, 12:30   #3
gazzae
Inactive
 
gazzae's Avatar
 
Join Date: Jun 2003
Location: Belfast
Age: 46
Posts: 4,594
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
Re: excel 2002 question ( dates )

Quote:
Originally Posted by paulyoung666
is it possible to have a cell in excel react to a calender , what i want is a spreadsheet that will change the font colour of a cell when a calender date is exceeded , i think that makes sense anyway

answers on a postcard please , and as usual , large greenies for helpful posts
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
gazzae is offline   Reply With Quote
Old 15-06-2005, 12:43   #4
Salu
Inactive
 
Salu's Avatar
 
Join Date: Jun 2003
Location: Yorks
Age: 59
Services: VM TV package. VM phone and 200MB internet & slow Tivo
Posts: 2,332
Salu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronze
Salu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronzeSalu is cast in bronze
Send a message via MSN to Salu
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.
Salu is offline   Reply With Quote
Old 15-06-2005, 13:29   #5
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
Re: excel 2002 question ( dates )

thanks for the answers so far , not totally sure where i need to go though
paulyoung666 is offline   Reply With Quote
Old 15-06-2005, 18:43   #6
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
Re: excel 2002 question ( dates )

for the terminally thick , can someone point me in the right direction please
paulyoung666 is offline   Reply With Quote
Old 15-06-2005, 18:48   #7
Raistlin
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
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
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?
Raistlin is offline   Reply With Quote
Old 15-06-2005, 19:15   #8
gazzae
Inactive
 
gazzae's Avatar
 
Join Date: Jun 2003
Location: Belfast
Age: 46
Posts: 4,594
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
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.
gazzae is offline   Reply With Quote
Old 15-06-2005, 19:36   #9
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
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
paulyoung666 is offline   Reply With Quote
Old 15-06-2005, 19:42   #10
Raistlin
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
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
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.
Raistlin is offline   Reply With Quote
Old 16-06-2005, 22:25   #11
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
Re: excel 2002 question ( dates )

thanks to the code from gazzae and the expert advice of raistlin for showing me what to do with it , i am almost there , what i need now is some way of stopping the gridlines from disapearing when the file is opened and closed
paulyoung666 is offline   Reply With Quote
Old 16-06-2005, 22:32   #12
Raistlin
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
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
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.
Raistlin is offline   Reply With Quote
Old 16-06-2005, 22:33   #13
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
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 ????
paulyoung666 is offline   Reply With Quote
Old 16-06-2005, 22:37   #14
Raistlin
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
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
Raistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered starsRaistlin is seeing silvered stars
Re: excel 2002 question ( dates )

You still about? I've got an interesting twist for you.....

Want me to email you with it?
Raistlin is offline   Reply With Quote
Old 16-06-2005, 22:39   #15
paulyoung666
Permanently Banned
 
paulyoung666's Avatar
 
Join Date: Jun 2003
Location: norton , teesside
Age: 58
Posts: 10,571
paulyoung666 has a nice shiny star
paulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny starpaulyoung666 has a nice shiny star
Re: excel 2002 question ( dates )

can do , or jump into chat if you want , upto you
paulyoung666 is offline   Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 06:14.


Server: lithium.zmnt.uk
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2026, vBulletin Solutions Inc.
All Posts and Content are © Cable Forum