Forum Discussion
Is it possible to create a macro for conditional formatting?
So I have conditional formatting rules located throughout my spreadsheet, but every time I add a row, the formatting in the rest of the columns doesn't apply to the new row. I simply use format painter to grab what I want to replicate and paste it where I need it.
Here's the problem, when I go to the manage rules portion of the conditional formatting tool, it lists my original formatting range.... plus every.... single.... copy and pasting request I've made over time. My list of rules is hundreds of pages long, and they're riddled with duplicates.
Is it possible to create a macro for conditional formatting, whereby you can edit a script detailing where you want which formatting rules to apply, and when? Seems like it would be cleaner.
Thank you!
8 Replies
One workaround is to turn a range in which you use conditional formatting into a table. Formulas and Conditional Formatting should be propagated to new rows automatically.
But it's definitely possible to create conditional formatting rules in a macro. But since there are so many different types of conditional formatting, it's hard to provide a one-size-fits-all solution.
Thank you both.
The attached represents the objective. Essentially applying colors to text for the most part.
See the attached workbook (you'll have to allow macros). I changed the layout of the CF sheet slightly.
There is a macro that will create or recreate the conditional formatting rules for the Data sheet.
Danger_SF so inserting rows should include the formatting but I know the whole conditional formatting is touchy and yes can result in numerous copies of rules an such. As such, about a year ago I created just such a workbook. The attached workbook has a sheet called conditional-formatting with a number of macros that will enable you to 'read' the existing CF rules, clear all the existing CF rules, and Apply the 'rules' defined in that worksheet. hover over row 1 to see how that column of information should be formatted.
I include no guarantees but would appreciate feedback and if there is interest I could continue work on it.
