How can i make this conditinal formatting code shorter?

[tonywatsonhelp]

Hi Everyone,
I have recorded this vba with the macro recorder but it seams a little long, does anyone know how i could cut it down,
I want to use it as the template for all my conditional formatting vba that I'm going to need so having something easy to edit and that is written more compactly would be a huge help,

thanks

Tony
here's my code

Sub LegalCF_for_Row38()

    Range("E38:H38").Select
 
  
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF($E$38=""Select Hospital Records"",TRUE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Fluff]

How about

Sub LegalCF_for_Row38()

   With Range("E38:H38")
      .FormatConditions.Delete
      .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=$E$38=""Select Hospital Records"""
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1)
         With .Font
            .Color = -16776961
            .TintAndShade = 0
         End With
         With .Borders
            .LineStyle = xlContinuous
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
         End With
         With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
         End With
         .StopIfTrue = False
      End With
   End With
End Sub

 

[tonywatsonhelp]

Thanks fluff,
is this as short as conditional formation code can get?
is there a better way of doing it

 

[Fluff]

Why the need to shorten it?

 

[tonywatsonhelp]

I'm trying to create a template stle macro that i can coopy and edit nice and quickly

I find it very handy having the macro redo the conditional formating of a section sometimes as vba can mass it up, so what I was hoping to have is my go to formates that i can have saed for examlpe "RED font" orange background, Red border, and i can just copy and paste them when i want to use it and change the range.

so i just wanted to keep it as simple as possible but happy with what you did if thats the best it can be, i just know the macro recorder offten includes a lot of defualt setting we dont need, when i did my borders and file vba emplate o got it down to just a few rows, which is nice a quick to glance overwhen chnaging and using it.

 

[Fluff]

It could probably be slimmed down a bit more, but I tend to keep it like that.

 

[Joe4]

If you want something that is easily re-usable, you can simply make range and color variables that you define at top your code, and then replace all the instances of those values in your code with the variable names. Then all you would have to do to re-use the code os change the values of those two variables at the top of your code.

 

[tonywatsonhelp]

thats the sort of thing i'm talking about Joe, but i et lost trying to do it? any ideas?

 

[Joe4]

Something like this at the top of your code:

Dim rng as Range
Dim clr as Double

'***Set variables***
Set rng = Range("E38:H38")
clr = -16776961

Then you replace all the instances of your range in the code, like replacing this:

With Range("E38:H38")

with this:

With rng

and replacing your instances of color on your code like this:

.Color = -16776961

with this:

.Color = clr

So it just makes it a little easier to manage, as they only have to update the variables at the top of the code, and don't need to search through the entire code.

 

[tonywatsonhelp]

Brilliant,
thank you,
its what i wanted just wasn't sure how to start as it where, this is great thank you
ow i can set all the bit i edit at the top of the macro and make dits super quick.
thanks very much for your instructions
Tony