Share Conditional Formatting Rules Between All .xlsx and .xlsm Files

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Suppose I have some special conditional formatting rules set up that I would like to be able to use in ALL of my Excel documents, whether they be .xlsx or .xlsm. Is it possible to create such a rule and have it accessible to any document that I open? If so, how? Would you be able to provide an example? Thank you very much.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Conditional Formatting should work in xlsx or xlsm without any issues.
Just about the only way I can think of doing what you want is to create some VBA code that sets up the Conditional Formatting the way you want, and save that code to your Personal Macro Workbook. Then that code should be available to you on your computer whenever you have Excel open. Note that you will need to run that code on any new workbook to apply it to that new workbook.
If you want something already built in, you probably want to create an Excel template file that already has that built-in, and create your new files from the template.
 
Upvote 0
Ideally, if I wanted to make some kind of change, it would apply to all of the different .xlsx and .xlsm files that use those conditional formatting rules, so it seems like the personal workbook is the only way to go here. However, I'm still not exactly sure how I would do that; can you provide an example?
 
Upvote 0
If you record yourself manually applying the Conditional Formatting rules to a workbook, you will have the VBA code that you need. Then you can store it in a Personal Macro Workbook, as shown here: https://support.office.com/en-us/ar...workbook-aa439b90-f836-4381-97f0-6e4c3f5ee566

Note: You still need to run that Macro on any new workbook in order to add the Conditional Formatting rules to it. It just saves you the steps of having to do it all manually.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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