Highlighting a cell when a date comes
Highlighting a cell when a date comes
(OP)
I have an Excel sheet I use to manage numerous projects. I have the project tasks in the first column, and then the different projects along the top row. When a new project comes along I add the project name in the next column in the top row & I input dates i need to do the tasks by by writing ' by 5/7/05 ' for example.
I have to review the sheet daily to see if any deadlines are looming. My question is - Is there a way that when the date occurs (ie it is the 5/7/05), the cell background colour automatically changes to red, thus highlighting that particular task is due. The solution must include the 'by' word, to indicate the task should be done by then
Help on this would be very much appreciated. - Thanks
I have to review the sheet daily to see if any deadlines are looming. My question is - Is there a way that when the date occurs (ie it is the 5/7/05), the cell background colour automatically changes to red, thus highlighting that particular task is due. The solution must include the 'by' word, to indicate the task should be done by then
Help on this would be very much appreciated. - Thanks





RE: Highlighting a cell when a date comes
B15 contains "by 6/29/2005"
=IF(ISERROR(FIND("by",B15)),"not by",IF(ISERROR(FIND("6/29/2005",B15)),"not today","TODAY")) ... this equation will put TODAY if its today
i think you can replace "6/29/2005" with text(today())... so that the equation is
=IF(ISERROR(FIND("by",B15)),"not by",IF(ISERROR(FIND(text(today()),B15)),"not today","TODAY")) ... but that isn't working for some reason
You should be able to put these equations into conditional formating to change the cell color...
I will be interested to see what other people come up with.
RE: Highlighting a cell when a date comes
Now you can use conditional formatting to get the Red background. In an unused cell (for this example A28) type in '=TODAY()' (without the single quotes). Select all the date cells then go to Format|Conditional Formatting. Set Condition 1 as Cell Value is equal to =$A$28. Click the adjacent Format button, select the Patterns tab and choose your colour.
That's it - any cell in the chosen range with today's date will have your Red background!
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Highlighting a cell when a date comes
Condition 1: If Cell Value < $A$28 (i.e. task is now overdue) you might use bold font in a red cell.
Condition 2: If Cell Value = $A$28 (i.e. task is due today), you might use another colour code (maybe a green cell).
For all other cases (not yet due), just use default format (no conditional format needs to be set). Anything which has default formatting is therefore not yet due.
RE: Highlighting a cell when a date comes
=DATEVALUE(RIGHT(TRIM(B15),LEN(TRIM(B15))-3))
should return only the date portion of the "by 6/16/06" date. Then you could use date functions as needed in conditional formatting.
Life would be much easier if your column heading could be "due by", and you dispense with the "by" in each cell.
Or better yet, use an actual scheduling program like MS Project or Primavera.
RE: Highlighting a cell when a date comes
You may find it's a lot easier to store the actual date and use the Custom Format to display the 'By ' as shown in my post above!
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Highlighting a cell when a date comes
However, this has highlighted another issue. Once the task is complete, I usually type 'Yes 30/6/05', for example if the task was completed today. With the conditional formating, this cell is still the conditional formated colour.
I'm thinking... Could there be a 'hotkey' (for eg ctrl-T) that holds a macro (?) that would clear the conditional formatting and enter Yes and todays date automatically.
Any Ideas????
RE: Highlighting a cell when a date comes
Good Luck. - Ed
RE: Highlighting a cell when a date comes
for conditional format, select Format-Conditional Formatting and proceed from there.
furthermore, there are many websites that offer tips for using conditional formats.
good luck!
-pmover
RE: Highlighting a cell when a date comes
This will also probably keep your file size down by reducing the complexity of the formulae in your cells.
This code should work if you copy and paste it into your VB Editor and just change the starting cell to the first date field you have. Of course, it'll fall over if you have any blank date fields midway down your column, but you can change the loop criteria to the limits of your date field.
You can also obviously change the formatting of the fields by changing the color numbers in the format code.
CODE
Dim intDate# 'declare variable intDate as integer
Dim intToday# 'declare variable intToday to hold today's date
intToday = Date 'set today's date
'Date is the function for the system date on your computer
'move to the first cell in your date column
Range("P1").Select
'initialize intDate
intDate = Range("P1").Value
'start a loop to go down your spreadsheet date field.
'the date field is an integer so we want > 0
'not = "" to denote empty cell
Do
If intDate > intToday Then 'task is due in the future
'set format to black
Selection.NumberFormat = "m/d/yyyy;@"
With Selection.Font
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = xlNone
ElseIf intDate = intToday Then 'task is due today
'set format to red fill and white text
Selection.NumberFormat = "m/d/yyyy;@"
With Selection.Font
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ElseIf intDate < intToday Then 'task is overdue
'set format to red text on yellow fill
Selection.NumberFormat = "m/d/yyyy;@"
With Selection.Font
.ColorIndex = 3
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else 'there is no other option, but oh well
Exit Do 'get out of the loop, something's wrong!!
End If 'end your if statement!
ActiveCell.Offset(1, 0).Select 'go to next cell in column
intDate = ActiveCell.Value ' set intDate = date in the new cell
Loop Until intDate = 0 'terminate your loop statement
'remove your variables from memory
intDate = Empty
intToday = Empty
End Sub
Good luck!