CODEWITHSUNDEEP
excelvba
Gordon
Gordon

Reputation: 1651

conditional formatting in Excel to highlight important dates

I have an excel worksheet with hundred of dates. Is there anyway to conditional format those cells to highlight dates that are fast approaching in red, and dates far in the future in green?

Upvotes: 0

Views: 2145

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38530

You don't need VBA to do this. In my opinion, VBA will unnecessarily complicate things in this particular case. (This coming from a VBA enthusiast of sorts.)

enter image description here

This is Excel 2003, but the idea is the same in 2010.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152596

What version of Excel? In Excel 2010 (and I presume 2007) you can use a Graded Color Scale starting at Red and changing to Green for your range of dates. In fact a Red-Yellow-Green scale is one of the options under the "Conditional Formatting" ribbon item.

To set conditional formatting using VBA use a macro like this:

Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
    xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
    .Color = 8109667
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
    xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
    .Color = 8711167
    .TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
    xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
    .Color = 7039480
    .TintAndShade = 0
End With

Upvotes: 2

Related Questions