+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    04-24-2019
    Location
    Seattle, WA
    MS-Off Ver
    365
    Posts
    4

    Question 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!
    Last edited by Hellogiraffe; 04-24-2019 at 02:42 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

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

    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!
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  3. #3
    Registered User
    Join Date
    04-24-2019
    Location
    Seattle, WA
    MS-Off Ver
    365
    Posts
    4

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

    Quote Originally Posted by Melvosh View Post
    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!
    Thanks for the quick response, but it doesn't quite answer the question. The underlying issue seems to come from the $ absolute column in the formatting formula such that each cell in the header row is only looking at the single call-out cell. When I copy/paste over to the next mini-table on the right, the headers are still looking at the original call-out: B1. I can only move up and down with the format copies, not left and right.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

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

    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.

  5. #5
    Registered User
    Join Date
    04-24-2019
    Location
    Seattle, WA
    MS-Off Ver
    365
    Posts
    4

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

    I don't know why I didn't think about an offset function based on column number. I can't check the exact clunker formula for about an hour or so, but I'm sure that I can make it work. Thank you so much!

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

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

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting - copy and paste
    By KyronQ6 in forum Excel General
    Replies: 3
    Last Post: 07-03-2017, 10:18 AM
  2. [SOLVED] Copy A Conditional Formatting Rule In Multiple Cells In One Column
    By Jim Parker in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-03-2015, 03:55 PM
  3. Replies: 2
    Last Post: 12-29-2013, 05:53 AM
  4. How to separate out groups of cells based on conditional formatting AND account #?
    By coco1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-26-2012, 01:34 PM
  5. [SOLVED] Copy and paste cells with Conditional Formatting
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2012, 02:38 PM
  6. Conditional Formatting Copy & Paste
    By adam2308 in forum Excel General
    Replies: 2
    Last Post: 06-07-2010, 06:03 PM
  7. Copy Paste Conditional Formatting
    By Bud Hughes in forum Excel General
    Replies: 6
    Last Post: 10-01-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1