Conditional Formatting Formula with VBA for Multiple Columns

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
130
I have a Conditional format for Range: E7:F90

The formula is
Excel Formula:
=IF(INT((G7-C7)*24)<=4,TRUE,FALSE)

If True Shades the G:C cell in a color

I'm trying to setup a macro and was able to find something that partially works. It does color my true statements, but it also highlights cells that are false

VBA Code:
Sub setCondFormat()
    Range("E7").Select
    With Range("E7:F90")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(INT((G7-C7)*24)<=4,TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
        End With
    End With
End Sub
A7B7C7D7E7F7G7
JobNameStart TimeEnd Time
ExamplePersonA9:00COLORCOLOR11:00
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
As you are formatting multiple cells you need to lock the columns in the equation, also with conditional formatting you don't need the If function, try using
Excel Formula:
=INT(($G7-$C7)*24)<=4
 
Upvote 0
As you are formatting multiple cells you need to lock the columns in the equation, also with conditional formatting you don't need the If function, try using
Excel Formula:
=INT(($G7-$C7)*24)<=4
Oh Awesome! That's exactly what my problem was. Do you know if adding XXX to that same condition can be done? Like Color & Text?
 
Upvote 0
Not sure what you mean by

Can you please explain?
Actually I can skip that process for now. So I just ran script and I realize because this particular day my data stopped at row 42 instead of 90; everything from E43:F90 was highlighted in the conditional formatting. Each day the amount of rows varies, should I have set it a different way?
 
Upvote 0
Rather than hard coding the range, uou can set the used range like
VBA Code:
    With Range("E7:F" & Range("C" & Rows.Count).End(xlUp).Row)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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