MrExcel.com - Your One Stop for Excel Tips & Solutions

Highlight Due Dates

May 28, 2002 - by

Ed asked this week's Excel question.

I need a simple Excel macro that will search - lets say - Column C and highlight red the cells that contain today's date (current date) and highlight yellow any other cell in the column that is 15 days in the future from todays date?

Users of Excel 97 or Excel 2000 can take advantage of the new Conditional Formatting option. Let's cover how to do this manually without a macro first.

  • Move to cell C1.
  • From the menu, choose Format, Conditional Format
  • In the left side of the dialog, change the drop down to read "Formula is"
  • In the right side of the dialog box, enter: =INT(C1)=TODAY()
Setting Conditional Formatting
Setting Conditional Formatting
  • Click format, Click Patterns, pick Red. Click OK
  • Click Add...
  • In the left side of the dialog, change the drop down to read "Formula is"
  • In the right side of the dialog box, enter: =AND(INT(C1)>TODAY(),(INT(C1)-TODAY())<16)
  • Click Format, Click Patterns, Pick Yellow. Click OK.
  • Click OK to finish assigning this conditional format to cell C1.
Conditional Format Result
Conditional Format Result

If the format is entered correctly, cell C1 will change to red if it contains today's date and to yellow if the date is in the next 15 weeks. The TODAY() function in the format will insure that if we open the workbook on another day, it will highlight in red the cells for that particular day.

You can now copy cell C1, highlight all of the data in column C and do Edit > Paste Special > Formats > OK to apply that format to each cell in column C.

The following macro will automate assigning the conditional format: 

Sub Macro2()
    Range("C1").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=INT(C1)=TODAY()"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(INT(C1)>TODAY(),(INT(C1)-TODAY())<16)"
    Selection.FormatConditions(2).Interior.ColorIndex = 6
    Selection.Copy
    FinalRow = Range("C15000").End(xlUp).Row
    Range("C2:C" & FinalRow).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
End Sub

For Excel 95 users, you do not have conditional formatting, but could use a macro like this: 

Sub Macro95()
    ThisDate = Date
    FinalRow = Range("C15000").End(xlUp).Row
    For x = 1 To FinalRow
        ThisCell = Int(Range("C" & x).Value)
        If ThisCell = ThisDate Then
            Range("C" & x).Interior.ColorIndex = 3
        Else
            DaysFromNow = ThisCell - ThisDate
            If DaysFromNow > 0 And DaysFromNow < 16 Then
                Range("C" & x).Interior.ColorIndex = 6
            End If
        End If
    Next x
End Sub

The formula feature of the conditional format function is very powerful and will let you highlight cells that fit various criteria.