Conditional formatting by VBA

X

xrobc

New Member
Joined
Feb 25, 2015
Messages
27
Hi All

I am applying some Conditional formatting by VBA to both the same selection and different selections.

As most of the code is the same every time, I was hoping to be able to shorten the existing code:

Code: 
Sub Status_CF()

    'Status = U
    Range("I2:I1048576").Select
    Range("I2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""U"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = rgbRed
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F2").Select
    
    'Status = N
    Range("I2:I1048576").Select
    Range("I2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""N"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = rgbRed
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F2").Select
    
    'Not Found
    Range("I2:I1048576").Select
    Range("I2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""Not Found"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 130, 0)
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F2").Select


End Sub

Presumably I could use variables for the code that changes. Setting a range variable for each range but I'm not sure how I would do this.

Any help greatly appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
The following does make it a little shorter, but more importantly, gets rid of all those "Select" and "Selection" statements, which should improve efficiency and performance:
Code: 
Sub Status_CF()

    With Range("I2:I1048576")

        'Status = U
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""U"""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = rgbRed
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    
        'Status = N
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""N"""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = rgbRed
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    
        'Not Found
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$I2=""Not Found"""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 130, 0)
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
        
    End With

End Sub
 
Upvote 0
OK, thanks, I think I follow that.

If I wanted to run this code:

Code: 
Sub CCAR_CF()

    Range("K2:K1048576").Select
    Range("K2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K2=""Yes"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 130, 0)
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F2").Select

    Range("L2:L1048576").Select
    Range("L2").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L2=""Yes"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 130, 0)
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F2").Select



End Sub

Is there anyway that I could shorten that (same code, different range)?
 
Upvote 0
Is there anyway that I could shorten that (same code, different range)?
Click to expand...
Yes, it appears that it should follow the exact same methodology I did on your first run.
Compare your original to what I did, and then try to apply the same logic to this.
This will be a good test, and should be educational for you.

If you run into problems, post back here.
 
Upvote 0
OK, so this seems to work:

Code: 
Sub CF2()

    With Range("J2:J1048576")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$J2=""Yes"""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 130, 0)
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With


    With Range("K2:K1048576")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$K2=""Yes"""
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 130, 0)
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With


End Sub

Basically, here you are applying the Conditional formatting to a range rather than selecting a range and applying the conditional formatting to that selection.

Is there any way that I could use a variable to define the range and the conditional formatting formula, so I run the same code twice using the variables rather than running two blocks of code?
 
Upvote 0
I think I've cracked it:

Code: 
Option Explicit


Public Rng As String
Public CF_Form As String
Public CFCol As Long

Sub CF1

    Rng = "I2:I1048576"
    CF_Form = "=$I2=""N"""
    CFCol = rgbRed
    CF2
    
    CF_Form = "=$I2=""U"""
    CF2

End Sub

Sub CF2()


    With Range(Rng)
        .FormatConditions.Add Type:=xlExpression, Formula1:=CF_Form
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = CFCol
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
870
Micron
M
S
  • Question Question
Run VBA code from specific sheet till the end of workbook
Replies
7
Views
281
Alex Blakenburg
A
K
  • Question Question
Variable losing set Range
Replies
8
Views
456
MARK858
MARK858
S
  • Question Question
excel - conditional formatting - only for the last 50 rows of the tabl
Replies
3
Views
320
Vogateer
V
V
  • Question Question
runtime error 1004 in conditional formatting macro recording
Replies
3
Views
299
valmir
V

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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