Excel: Condtional Formatting Macro


Hi All, I posted this one on another forum but haven't had any assistance, so thought I would give it a shot over here. With little experience, I have created a macro to format a report I regularly download in a specific way. I have got to a point where I can't figure out how to carry on to the next step, and was hoping someone might help out. What I have, is a document full of times. There is a "scheduled time" and then a time for each day. I have conditional formatting setup to shade the cells depending on the comparison to the scheduled time. If a time is more than (or equal to) 4 minutes later than the schedule time, it is shaded red. If it's less than 4 minutes, it's shaded blue. If it's more than 1 minute earlier than the scheduled time, it's shading green. This is all working sweet. Now what I want to do is apply this conditional formatting throughout the entire document. The caveat here is, that it only needs to apply it to some rows, not all. The rows that it is applied on, it needs to be applied from cell H through cell AD. The part of the macro I have done that will format this way, for Row 2, is as follows...

Range("H2:AC2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=H2-$E2>""0:04""+0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Range("H2:AC2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=H2-$E2<""0:04""+0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Range("H2:AC2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MROUND(H2-$E2,""0:00:01"")=""0:04""+0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Range("H2:AC2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$E2-H2>""0:01""+0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
It's probably abundantly clear that I did this with "Record Macro" but it works fine to format Row 2 as I want it. The step I am stuck on now, is implementing this over the entire document. The common denominator for rows that require the formatting is whether or not there is anything written in column D. If there is data in column D, then that row needs to be formatted from H to AD. If there is no data in column D, then no formatting is required. I have gotten this far with my limited knowledge, but have come to a grinding halt. If anyone could point me in the right direction, I'd be most appreciative. Thanks all! -S_Hart


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of April 15, 2015.


For more resources for Microsoft Excel