Create a Master Format for Use on Multiple Ranges

[Eric G]

How do I create a master list of formatting attributes for the purposes of affecting multiple ranges simultaneously?

For example, let's say I want the same format for the following defined ranges:

Dim r2,r3 As Range

Set r2 = Sheets("2").Range("B3:I502")
Set r3 = Sheets("3").Range("B3:B1002")

I tried:

Dim MasterFormat As String

With MasterFormat
    .ClearFormats
    .Borders.Weight = xlThin
    .Font.Size = 10
    .Font.Name = "Arial"
    .VerticalAlignment = xlCenter
    .Locked = False
End With

r2.MasterFormat
r3.MasterFormat

Instead of success, I receive the following message: "Compile error: With object must be user-defined type, Object, or Variant"

Any suggestions on how to accomplish what I am trying to do?

 

[JLGWhiz]

I doubt that VBA would support that type of action, since the target objects are separate entities and the format attributes are are also separate entities of Font, Borders and Allignment. You could probably use a combination of array and loop and get pretty close to what you describe.

 

[Eric G]

Thank you for responding.

Would it help if all I wanted to manipulate was:

.Font.Size = 10
.Font.Name = "Arial"

And if that is still too much. What if just:

.Font.Size = 10

Is it possible then?

 

[Fennek]

Hello,

untested:
select a cell outside the usedrange (e.g. AA1) and do all formaitings:

Range("AA1").copy
Range("A2:A20").pastespecial xlformats

regards

 

[Eric G]

Thanks Fennek for responding, but unfortunately, I am looking for the formatting to come from the coding itself. So that whenever I make a change to say the font color or font size, the change would affect all of my predesignated ranges. As it stands now, each designated range I have defined has its own formatting coding. It seems more efficient to change the formatting code in a single location than to go down all of the instances of where I've written the formatting code and make those changes.

Surely, someone out there has a stellar solution.

 

[Fennek]

do you mean something like this:

https://msdn.microsoft.com/en-us/library/office/aa224475(v=office.11).aspx

 

[JLGWhiz]

Maybe this is what you are looking for.

Sub t()
Dim r1 As Range, r2 As Range, myRange As Range
Set r1 = Sheet1.Range("A2:D5")
Set r2 = Sheet1.Range("A10:D14")
Set myRange = Union(r1, r2)
MasterFormat myRange
End Sub

Sub MasterFormat(ByRef rng As Range)
    With rng
        .ClearFormats
        .Borders.Weight = xlThin
        .Font.Size = 10
        .Font.Name = "Arial"
        .VerticalAlignment = xlCenter
        .Locked = False
    End With
End Sub

The first code would be your basic code and the second is code you would call to do the formatting of specirfic ranges. You can use the Union to do more than one range at a time, but the ranges in the union must be in chronological order or it will tilt.

 

[Eric G]

The style idea is intriguing. I'll have to research it more tomorrow.

As for the union idea -- what a tease! Not you, as I do appreciate the attempt, but as I understand it, unions are limited to ranges within a single sheet, and they cannot be used for different ranges spanning across multiple sheets. If I am wrong, please enlighten me. I'm most eager for a hallelujah moment.

In the end, I may continue with what I have. It works. Just it would be great to control all of those separate ranges from a single control box, if you will. Nonetheless, thanks gentlemen for banging your heads against the wall on this one. Frustration loves company.

 

[mikerickson]

Let me endorse the Style reference. You can do this without VBA through Styles.

 

[JLGWhiz]

The style idea is intriguing. I'll have to research it more tomorrow.

As for the union idea -- what a tease! Not you, as I do appreciate the attempt, but as I understand it, unions are limited to ranges within a single sheet, and they cannot be used for different ranges spanning across multiple sheets. If I am wrong, please enlighten me. I'm most eager for a hallelujah moment.

In the end, I may continue with what I have. It works. Just it would be great to control all of those separate ranges from a single control box, if you will. Nonetheless, thanks gentlemen for banging your heads against the wall on this one. Frustration loves company.

Yes, Union is a limited method. It cannot be applied across multiple sheets in a single shot, but could be applied in a loop. Styles might be your best shot for what you are attempting, but that might also be limited if you are only applying it to randomly located ranges in different sheets.