Condtional Formatting Macro

[S_Hart]

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

 

[S_Hart]

Is column D being manually put in, coming from formulas, copied and pasted, etc?

Yes, the entire data block has been copied and pasted. Generally, this is because I have to download the report outside the remote desktop window I am working in, therefore simply copy the data and paste it into Excel inside the remote desktop window.

Having said that, it doesn't seem to make a difference. If I download a report and immediately apply the formatting, it still seems to be reading something in the cell despite it appearing empty. So it's nothing I've done to make it seem that way, that's the way it is when the report downloads.

 

[S_Hart]

Looking at it further, yes, it would seem that any cell in column D that appears blank, actually does have a single space at the start.

I have therefore gone ahead and changed the formula from "" to " " with a space, and now it seems to be doing the job.

I'll put it all together and report back.

 

[S_Hart]

Ok, so it's working really well, just one little tweak at the moment I am trying to make.

I have the range set as H2:AD10000. Given the reports are a month, and don't contain weekends, the highest it will ever go is AD. Row wise, it depends how long the report is, I just dumped in 10000 because I don't think it goes anywhere near that high ever.

Now, with the conditional formatting, if there are cells that are missing a time for any given reason, or displays N/A, then no formatting is applied. This is perfect, I don't want cells without time to be formatted. I guess because the cell isn't "blank" (ie. it has the space) and it doesn't find a time to format, it just ignores it. That's fine. The problem is the cells at the side and bottom beyond the range.

For example, it's set to H2:AD10000, but the current one I am working on only goes to AC. On the lines that the conditional format is taking place, (ie. the ones with data in row D) it is applying the data to the blank cells. I guess because the cells are actually truly blank, it's reading it differently and applying formatting. Therefore, on any row that has formatting, AD is shaded green. And down the bottom when the data finishes, every cell is shaded blue.

Is there any way to only apply the conditional formatting to rows that have a value, and ignore the blank ones?

It's a minor issue in the scheme of things, but for the sake of neatness, it would be nice not to have unnecessarily shaded cells everywhere!

In case I didn't explain it well, here's a link to an image showing what's occurring - http://i.imgur.com/DnEnMzM.jpg

 

[S_Hart]

Finally dawned on me that given everything outside the data is blank, but everything within the data field that appears blank has a space, that I could solve the problem just by adding a conditional that anything containing "" on the entire worksheet should be blank.

Problem solved.

Many thanks for all the help.

 

[lrobbo314]

Glad to see that you got it worked out.

 

[S_Hart]

Glad to see that you got it worked out.

I was wayyyy over thinking it. Thanks for pointing me in the right direction.

Much appreciated!