Condtional Formatting Macro

S

S_Hart

New Member
Joined
Apr 15, 2015
Messages
11
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...

Code: 
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
How about this?

Code: 
Sub Hart()
Dim LR  As Long
Dim r As Range
LR = Range("D" & Rows.Count).End(xlUp).Row()

If Range("D" & LR) <> "" Then
    For LR = LR To 2 Step -1
        Set r = Range("H" & LR & ":" & "AC" & LR)
            With r
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=H2-$E2>""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 255
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=H2-$E2<""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 15773696
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=MROUND(H2-$E2,""0:00:01"")=""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 255
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=$E2-H2>""0:01""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 5287936
                .FormatConditions(1).Interior.TintAndShade = 0
            End With
    Next LR
End If
 
Upvote 0
lrobbo314 said:
How about this?
Click to expand...

Forgive my ignorance, but will it generally take a long time for something like this to run? I have about 1500 lines of data, and as soon as I try and run this Macro, Excel hangs and is "not responding."

Is something wrong, or should I be just letting it do it's thing?
 
Upvote 0
Yeah. It is going to take a really long time to go through 1500 lines unfortunately.

Can't you just apply it to the entire range? Maybe add another conditional format in there that says if $D2 = "" then no fill.

This code adds it to the entire range in one fell swoop.

Code: 
Sub MacroRecorder()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim LR  As Long
Dim r As Range
LR = Range("D" & Rows.Count).End(xlUp).Row()


        Set r = Range("H1:AC" & LR)
            With r
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=H2-$E2>""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 255
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=H2-$E2<""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 15773696
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=MROUND(H2-$E2,""0:00:01"")=""0:04""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 255
                .FormatConditions(1).Interior.TintAndShade = 0
                .FormatConditions(r.FormatConditions.Count).SetFirstPriority
                .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=$E2-H2>""0:01""+0"
                .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
                .FormatConditions(1).Interior.Color = 5287936
                .FormatConditions(1).Interior.TintAndShade = 0
            End With

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
lrobbo314 said:
Can't you just apply it to the entire range? Maybe add another conditional format in there that says if $D2 = "" then no fill.
Click to expand...

That's an interesting idea, and one I hadn't considered. I will give that a shot and see if that works better.

This code adds it to the entire range in one fell swoop.
Click to expand...

That one didn't quite work out for me, but I do have one that I have already made that applies it across the entire range. I'll try adding to not fill anything with text in column D and see if that solves the problem.

Will report back.
 
Upvote 0
lrobbo314 said:
Maybe add another conditional format in there that says if $D2 = "" then no fill.
Click to expand...

Any thoughts on what formula I should put in to achieve this no fill? If D2 is an empty cell, then no fill. And given it is the most important, I should have it at the top, correct? So that it will usurp any other fill? Or should it be last?
 
Upvote 0
Yes. On top. Also check the "Stop if True" box. Then the formula would just be

Code: 
=$D2=""
 
Upvote 0
lrobbo314 said:
Yes. On top. Also check the "Stop if True" box. Then the formula would just be

Code: 
=$D2=""
Click to expand...

That's what I figured, but it doesn't seem to be working. If I apply that rule, have it on top, and I had the "Stop if True" box checked, it is still carrying on and shading the cells following the rest of the rules.

D2 does in fact have text in it, so I am copying and pasting the "Format" to cells H5 through AC5, as D5 is the first example that is blank. But even with D5 blank, it is carrying on with the rest of the formatting. I guess the cell isn't "really" blank, even though nothing is in it?

I'm confused as to why it isn't working, because it seems like exactly what I was going for.
 
Upvote 0
Yeah, the issue is definitely with the contents of the cell being returned.

If I leave H5:AC5 with the formatting, right click on D5 and select "Clear Contents" - boom. All shading disappears.

So despite the cell appearing blank, Excel is returning a value of some kind. Is there any way around this?
 
Upvote 0
Are there spaces in the cells or something?

Is column D being manually put in, coming from formulas, copied and pasted, etc?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Loop Not Looping too Next Sheet
Replies
3
Views
239
Joe4
Joe4
tezza
  • Question
VBA conditional order issue
Replies
6
Views
447
kevin9999
K
E
  • Solved
Conditional Number Formatting a range based on a different cell value
Replies
3
Views
242
Edgarvelez
E
K
  • Solved
highlight numbers
Replies
7
Views
313
MARK858
MARK858
S
  • Question
AutoFill Macro Stops working after a short tim
Replies
3
Views
405
Flaiban
F

Forum statistics

Threads
1,214,813
Messages
6,121,706
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top