+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting Across multiple Cells Shortcut?

Hybrid View

  1. #1
    Registered User
    Join Date
    07-07-2019
    Location
    Pennsylvania
    MS-Off Ver
    365
    Posts
    25

    Conditional Formatting Across multiple Cells Shortcut?

    Hi All.

    I have 2 questions that I can not figure out RE Conditional Formatting

    *** I have attached an example spreadsheet with what I believe to be fairly detailed explanations and examples of what I am trying to accomplish and the issues I need to solve. Please let me know if this is enough info or not. I will summarize here as well:

    1) Confirming the exact formula that is to be input in order to conditionally FORMAT MULTIPLE CELLS BASED ON THE VALUE OF ONLY 1 OF THEM
    2) Find a way to simplify copying this Multiple outcome (3) Conditional Format to ALL other cell(s) , but have them copied to that cell as RELATIVE to itself, rather than just copying the conditional formatting of the original that was copied...Hope that makes sense. More details in the Attachment.

    *Side Note: If there is a way to solve #2, the only kicker is that I cannot use VBA as per my companies compliance restrictions...Hopefully there is a way to do this without it haha.

    Please let me know if possible, and as always - Thank you very much for the help in advance!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,852

    Re: Conditional Formatting Across multiple Cells Shortcut?

    First, in my opinion, this design has a few problems. I will give you an answer for the sheet as you have presented it but the design is working against you. What other data is in each block besides the name and number? First, Excel works better when you deal with rows and columns. Creating blocks like this is not at all friendly to developing formulas to populate or analyze the data. Second, using merged cells creates many problems. So my primary advice is to completely redesign this.

    Now, given the design as it is, here is the problem. You have a single conditional formatting rule for the "Jane Doe" block that uses an absolute reference to $C$6 (not C5, as your notes in the file repeatedly describe). As you have found you cannot apply that rule to the other blocks. The solution to this is to use relative addressing. Of course, this creates another problem, because that means that each cell in the block has to have its own rule to reference the correct relative position. You need four rules:

    B2: =C6<=5
    B4: =C6<=5
    B6: =C6<=5
    C6: =C6<=5

    Note: This will also fill with red if C6 is blank. I don't know if you will really have blank cells there.

    Once you have applied these rules to the first block, you can use the format painter to copy the entire block and paint the formats to all the other blocks at one time.

    I have done this as an example for the red.

    For Orange
    =C6<=9

    For green
    =C6<=15

    You must put these rules in the correct order for the appropriate one to be the one that takes effect (red first, then orange, then green). If you put them in the reverse order, then check "Stop" I think that will also work and may be more efficient.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. [SOLVED] conditional formatting, shortcut for highlighting cells
    By juco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2019, 09:04 AM
  2. [SOLVED] Conditional Formatting over multiple cells with multiple criteria validator
    By effendrew in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-31-2015, 05:05 PM
  3. [SOLVED] Conditional formatting multiple cells based on multiple cells
    By jhuvba in forum Excel General
    Replies: 2
    Last Post: 05-18-2015, 12:19 PM
  4. [SOLVED] Shortcut for numerous conditional formatting rules?
    By Butcher1 in forum Excel General
    Replies: 2
    Last Post: 11-24-2014, 01:01 PM
  5. [SOLVED] Conditional Formatting based on multiple strings in multiple cells
    By lily_ruiz_06 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2013, 04:23 PM
  6. Excel 2007 : Conditional Formatting shortcut
    By aLc319 in forum Excel General
    Replies: 1
    Last Post: 07-17-2009, 08:30 AM
  7. [SOLVED] colour due to conditional formatting & hyperlink/shortcut to a directory
    By Peter STEVENS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2005, 06:50 PM

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