+ Reply to Thread
Results 1 to 6 of 6

Workaround to Conditional Formatting based on a Format of another cell

  1. #1
    Registered User
    Join Date
    05-09-2013
    Location
    Dublin, OH
    MS-Off Ver
    Excel 2010
    Posts
    9

    Workaround to Conditional Formatting based on a Format of another cell

    Hey everyone, thanks for all the help, I've used this forum a lot but never posted. I've searched high and low and can't find an answer to my problem so I thought I would post!

    I have data in 2 columns, we'll say A and B, both with numbers only. All of the data in Column A has a straight forward conditional format to fill the cell with Red, Green, or Yellow based on what number it is. I want to automate (formula, conditional format, etc) the data in Column B to say: "If the cell directly next to you in Column A is Yellow, then also turn this cell in Column B Yellow".

    E.g. Cell A4 is 15, so the conditional format turns it Green. Cell B4, regardless of what data is in it, needs to automatically turn green because Cell A4 is green. Obviously I want this to be repeatable for many more cells in a larger data set......

    Thanks for any help you can offer!
    Andy

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Workaround to Conditional Formatting based on a Format of another cell

    cf format b with the same rules
    so format for a1 say is =a1=15 green
    make the format =$a1=15 green
    copy format from a1 to say a1:b100
    see example
    Attached Files Attached Files
    Last edited by martindwilson; 05-09-2013 at 07:48 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    05-09-2013
    Location
    Dublin, OH
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Workaround to Conditional Formatting based on a Format of another cell

    MOST EXCELLENT! Thank you kind sir, this has really helped out..... Excel users are the best.

  4. #4
    Registered User
    Join Date
    05-09-2013
    Location
    Dublin, OH
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Workaround to Conditional Formatting based on a Format of another cell

    Actually, there is one more thing I was hoping to be able to do. This CF works great, but I realized I need to be able to quickly apply it horizontally, to many other sets of 2 columns needing the same CF. I have attached an example that will explain this much better.

    In the example: I wrote the CF formula as instructed for Column's D and E. Now I want to be able to quickly apply the same thing for the combo's of F/G, H/I, and J/K (and in real life, there are many more. To apply it to the F/G combo, I C+P format from the D/E combo, and then manually went in to the F/G CF and changed the Formulas to refer to "$F2" instead of "$D2".

    This process works fine, but I am wondering if there is a simple faster way? If it starts getting into crazy formula writing, no worries, just thought I would see if I was missing a faster process...

    Thanks if anyone has any ideas!
    Attached Files Attached Files

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Workaround to Conditional Formatting based on a Format of another cell

    yep its a bit long as it uses index
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-09-2013
    Location
    Dublin, OH
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Workaround to Conditional Formatting based on a Format of another cell

    Wow, thank you so much for taking the time to write the formula. I sincerely appreciate it!

    Andy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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