Copy/Paste Conditional Formatting of Groups of Cells in Multiple Directions

Excel Question.JPG

Hey everyone,

I have a somewhat specific question about conditional formatting. As the simplified version of my issue shows, I have a bunch of mini-tables where the header rows need to be colored based on the contents of the call-out cell just above it. For example, G2:K2 turns yellow if H2 is "Bridge" or red if H2 is "Cat". My actual issue has about 36 of these mini-tables on a sheet, aligned in a 6 x 6 configuration, and has 8 different colors based on the contents of that call-out cell above the headers. Currently, all I can think of is doing 8 different rules for A2:E2 based on $B2 (absolute column so that the left-to-right headers don't need individual rules), copy/pasting the formatting on the 5 mini-tables below it, and then repeating the whole process for each column of mini-tables. That would make for 6 different groupings of 8 rules each. Is there any easy and clean way of formatting these, including via some form of VBA loop? I'm not too worried about putting in the extra work just to get it done but since it is part of a template that may be edited in the future, I would love an easier process so that I could maybe add or change a condition without having to modify 48 different rules.

Thank you!

You would only need the 8 rules. You can select non-contiguous ranges to apply the rules to. Click in the Applies to box, and while holding the ctrl key, select each range. Based on the picture, your Applies to box for one rule would look like this:

Please Login or Register  to view this content.

Hopefully that helps.

Edit:
I guess that really only helps with applying the rules, not the rules themselves. I'll be back with a solution for that!

Well, this is a kludge, and very specific to these ranges, but it should work. Try this for a conditional formatting formula, along with the previously mentioned Applies to:

Please Login or Register  to view this content.

That rule should work for Apple for any of your ranges, as long as they stay in these columns. You can then create rules for each of the other options, using the exact same formula, and just changing the "Apple" to whatever you need. There are plenty of factors that could throw this off, so let me know if you have any issues.

No problem, hopefully it works. I've been banging OFFSET formulas around all day, so it was in my head. Like I said, this one is clunky, but I think it could work. Good luck!