VBA, Macros and Conditional Formatting for Dates RRS feed

  • Question

  • I receive a weekly report of new changes, activities and initiatives. I manually scan this report and place the highlights in another report that I in turn review with several layers of my management teams. The critical data, for which I am trying to code the macro, are dates and date ranges. The activities are broken out in to multiple dates across multiple environments throughout the year, in typical change release calendar fashion. Any one activity can have one or a dozen or more dates across it's life span.

    As an example the dates for a single project would look something like this (the ordering is irrelevant as I want each individual date to format against today's date:

    TEST                         QA                              PRODUCTION

    8/1/2016	8/5/2016	10/2/2016
    1/22/2017	1/22/2017	8/7/2017
    7/23/2016	12/18/2016	3/11/2017

    I am trying to code an Excel macro that will conditionally format the cells to change the color according to the difference between the date listed in the cell and today's date. Such that anytime you open the spreadsheet the colors will always represent a span from "now" and show by color activities that are 'past due/3 days out/a week out/2 weeks/3 weeks/a month away'. I have used some or all of the following formulas:

    'I use this and change the last bit where: <=3 or <=7 or <=14 for larger spans =IF(ISNUMBER($A1),AND($A1>TODAY(),$A1-TODAY()<=21)) 'I use this for anything that is in the past =IF(ISNUMBER($A1),$A1<TODAY()) 'Then I apply some form of coloring (Yes I am using Merged cells in some locations... Selection.Merge Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(ISNUMBER(A1:C1),A1:C1<TODAY())" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.499984740745262 End With

    I've got this process just fine when applying conditional formatting manually in a column or block of cells. So my problem occurs when I am trying to create a macro that will setup a 'new entry' space, pre merge the cells needed, add highlights and borders, then pre format the cells that will eventually contain dates with the conditional formatting. Then I can manually add my information, click the macro button and a new section will be merged and formatted, rinse repeat..

    Something isn't translating when I attempt to manipulate the macro code. I am trying to use relative references and only use absolute references with the column values... Maybe this is the wrong approach entirely but after a couple days of trying and retrying I'm looking for some expert advice.

    Thank you,


    I'm new, please help. Begging for forgiveness in advance.

    Friday, July 22, 2016 5:46 PM

All replies

  • Jason,
    Re:  color important dates

    Comments and opinions that may apply...
    1.  A "typical change release calendar" would be typical only in your industry/company.
    2.  Using more than 3 colors changes a workbook to a coloring book.
         Red (serious), Blue (important), Green (ok for now) is the way I would go.
         (yellow font is hard to see)
    3.  Recording your actions, when manually changing a workbook, can help determine the code to use.
    4.  The report comes out weekly; does it really need to be dynamic?
         The individuals using it can/should/would make their own updates/changes.
    5.  Why not run a loop thru the dates section of your report and color the date appropriately.
         The code could look something like this...
    Sub FindDatesAndColor()
    Dim rngDates As Excel.Range
    Dim rCell As Excel.Range

    Set rngDates = Selection.Cells
    For Each rCell In rngDates
      If VBA.IsDate(rCell.Value) Then
        Select Case True
        Case rCell.Value - Date < 3
           rCell.Font.Color = vbRed
        Case rCell.Value - Date < 10
           rCell.Font.Color = vbBlue
        Case Else
           rCell.Font.Color = vbGreen
        End Select
      End If
    Next 'rCell
    End Sub

    That is probably enough opinion.

    Jim Cone
    Portland, Oregon USA
    (free & commercial excel programs)

    • Edited by James Cone Wednesday, September 21, 2016 10:17 PM
    Saturday, July 23, 2016 2:23 PM
  • Jim,

    Thank you for your reply. Unfortunately the opinions provided don't remedy the specifics of the issue I described.

    I ended up using a different conditional format and it works as expected.

    Conditional Formatting > Highlight Cells Rules > Between

    The answer was right in front of me as there is a cell at the top of the sheet that displays the current date with =TODAY(). Now to convert the formats to code and I'm done. 

    I'm new, please help. Begging for forgiveness in advance.

    Monday, July 25, 2016 8:09 PM