Conditional Formatting Formula with VBA for Multiple Columns

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
122
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
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
 

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
122
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
Not sure what you mean by
Do you know if adding XXX to that same condition can be done? Like Color & Text?
Can you please explain?
 

Beh162

Board Regular
Joined
Jan 15, 2015
Messages
122

ADVERTISEMENT

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,658
Messages
5,549,265
Members
410,905
Latest member
Extjel
Top