+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting - color all cells in a range if 3 different values appear anywhere

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2007-2008
    Posts
    9

    Conditional Formatting - color all cells in a range if 3 different values appear anywhere

    Hi,

    I'm having a problem with my conditional formatting rule applying to all cells in a range. I have a row of 20 cells, B3:U3 (rows 3 and 4 are actually merged together, not sure if this makes a difference), and in this row I will be entering "1", "2", and "S" one time each in any of the 20 cells. Once I enter one of each of those values, I'd like the rest of the cells in the range to turn grey to let me know that this row is complete (meaning within this range there is one "1", one "2", and one "S"). I used the following rule in the conditional formatting:

    =AND(AND(COUNTIF(B3:U3,1)=1,COUNTIF(B3:U3,2)=1,COUNTIF(B3:U3,"S")=1),B3<>1,B3<>2,B3<>"S")

    and tried to apply it across the range to the right, up to U3. However, if I enter a "1", "2", and "S" in any of the cells in the range, only the cells to the left of the first cell containing any of the values will turn grey. Is there a way to have all of the cells that are blank, both to the right and left and in between the three values, turn grey without changing the rule for every column?

    I've attached a sample row from the workbook for reference. Thanks for your help!
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Conditional Formatting - color all cells in a range if 3 different values appear anywh

    Make all the column references absolute:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2007-2008
    Posts
    9

    Re: Conditional Formatting - color all cells in a range if 3 different values appear anywh

    When I made ALL the column references absolute, the cells with "1", "2", and "S" greyed out too. I tried it again leaving the last 3 arguments (B3<>1,B3<>2,B3<>"S") the way they were though, and that seemed to do the trick. Thanks for your help!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Conditional Formatting - color all cells in a range if 3 different values appear anywh

    You're welcome. Thanks for the rep.

    Sorry, I thought you wanted the whole row coloured ... missed that



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  5. #5
    Registered User
    Join Date
    07-27-2014
    Location
    Mumbai, India
    MS-Off Ver
    excel 2010
    Posts
    1

    Re: Conditional Formatting - color all cells in a range if 3 different values appear anywh

    I have a similar issue, Pls can someone let me know equivalent macro code for 3 color code conditional formatting.

    I have data points from A1 to C3 ( total 9 data points having values from -0.9 to 1.1)

    What is the code to apply builtin conditional formatting already available in excel. No formula use is required.

    Thanks in advance.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Conditional Formatting - color all cells in a range if 3 different values appear anywh

    @mchouras: welcome to the forum.


    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ 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. Replies: 3
    Last Post: 05-30-2014, 10:24 AM
  2. Replies: 5
    Last Post: 10-24-2013, 05:34 PM
  3. Replies: 2
    Last Post: 07-01-2013, 05:01 PM
  4. [SOLVED] Conditional formatting to color only cells in one row
    By nivoe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2012, 05:54 AM
  5. Using Conditional Formatting: If cells contain, then color to?
    By davelarue in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2012, 02:22 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