Sort of excel calendar

P

pauluk

Ok this is what i would like to do, if any kind person could help:

I have i sheet with a description and a date which will be added t
when someone close the sheet i want the infor to be sorted by dat
order column b

Also i want the sheet to check the date in column b with the compute
date. if the date has past then that row is highlighted red.

i.e. the date the request was maded 26/05/04 Wed the sheet is open
today 01/06/04 then that row would be cloured red.

Thanks in advance
 
P

Pierre

just use this lines (assuming the range B1 contains the date you want t
compare to today:

Range("B1").Select
if range("B1")<>date then

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True

else

With Selection.Interior
.ColorIndex = xlNone
End With
Selection.Font.ColorIndex = 0
Selection.Font.Bold = False

endi
 
N

Norman Jones

Hi Paul

Right-click the Xl icon to the left of 'File' on your menus and paste the
following code into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Me.Sheets("Sheet1").Range("A1").CurrentRegion. _
Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

Sub workbook_open()
Dim LCell As Range
Dim i As Long

Set LCell = Me.Sheets("Sheet1"). _
Cells(Cells.Rows.Count, 2).End(xlUp)

For i = 2 To LCell.Row
If Cells(i, 2).Value < Date Then
Cells(i, 2).EntireRow.Interior.ColorIndex = 3
Else
Exit For
End If
Next i

End Sub
 
Top