Cable Forum

Cable Forum (https://www.cableforum.uk/board/index.php)
-   General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)
-   -   excel 2002 question ( dates ) (https://www.cableforum.uk/board/showthread.php?t=30326)

paulyoung666 15-06-2005 12:16

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 :erm:

answers on a postcard please , and as usual , large greenies for helpful posts :D :D :D

Wicked_and_Crazy 15-06-2005 12:26

Re: excel 2002 question ( dates )
 
=IF(TODAY()<C2,1,2)

where C2 holds the date

1 = true action

2 = false action

gazzae 15-06-2005 12:30

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 :erm:

answers on a postcard please , and as usual , large greenies for helpful posts :D :D :D

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


Salu 15-06-2005 12:43

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.

paulyoung666 15-06-2005 13:29

Re: excel 2002 question ( dates )
 
thanks for the answers so far , not totally sure where i need to go though :erm: :D :D

paulyoung666 15-06-2005 18:43

Re: excel 2002 question ( dates )
 
for the terminally thick , can someone point me in the right direction please :angel:

Raistlin 15-06-2005 18:48

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?

gazzae 15-06-2005 19:15

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.

paulyoung666 15-06-2005 19:36

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 :)

Raistlin 15-06-2005 19:42

Re: excel 2002 question ( dates )
 
1 Attachment(s)
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.

paulyoung666 16-06-2005 22:25

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 :erm: :D :D :D :D

Raistlin 16-06-2005 22:32

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.

paulyoung666 16-06-2005 22:33

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 ???? :)

Raistlin 16-06-2005 22:37

Re: excel 2002 question ( dates )
 
You still about? I've got an interesting twist for you.....

Want me to email you with it?

paulyoung666 16-06-2005 22:39

Re: excel 2002 question ( dates )
 
can do , or jump into chat if you want , upto you :D :D :D


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

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