Create a Master Format for Use on Multiple Ranges

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
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:

Code:
Dim r2,r3 As Range

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

I tried:
Code:
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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 21, 2017
Messages
47
Thank you for responding.

Would it help if all I wanted to manipulate was:
Code:
.Font.Size = 10
.Font.Name = "Arial"

And if that is still too much. What if just:
Code:
.Font.Size = 10

Is it possible then?
 

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hello,

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

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

regards
 

Eric G

New Member
Joined
Dec 21, 2017
Messages
47

ADVERTISEMENT

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.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Maybe this is what you are looking for.
Code:
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
Code:
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

New Member
Joined
Dec 21, 2017
Messages
47
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,904
Let me endorse the Style reference. You can do this without VBA through Styles.
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,310
Members
414,052
Latest member
Dual Showman

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
Top