+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting selective numbers

  1. #1
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Conditional Formatting selective numbers

    Hi,

    I am looking for a conditional formatting option where I can type a few formats for the following:

    If I have a table say 40 rows by 30 columns and random numbers within ranging from 1-20, I would like to set a Conditional format that say highlights specific numbers eg:-

    Any numbers in the range that have 1,7,8,11,13 highlight cell as green
    Any numbers in the range that have 2,4,6,8,10 highlight cell as red
    Any numbers in the range that have 3,4,5,14,15,16,17,20 highlight cell as blue

    I am sure there is any easy way, but any help much appreciated.

    Garry

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting selective numbers

    1. You have conflicting sets of numbers.

    In the first set of numbers you say highlight the cell as green. 8 is in that set so highlight it green.
    But 8 is also in the second set and now we're told highlight it as red.
    So should it be green or red?

    Same goes for second and third sets, 4 appears in both and we're told highlight as red, then highlight as blue ?

    2. The numbers 9, 12, 18 and 19 do not appear in any of your three sets of numbers.
    Presumably you don't want them highlighted?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Conditional Formatting selective numbers

    Use variants of this:


    =OR(A1=1,A1=7,A1=8,A1=11,A1=13)

    but 8 cannot be both red & green!!
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting selective numbers

    Once you've sorted out those number problems adjust the numbers in the formulas below

    Select the range (40 rows x 30 columns = A1:AD40)

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =(((A1=1)+(A1=7)+(A1=8)+(A1=11)+(A1=13))<>0)
    format as green

    =(((A1=2)+(A1=4)+(A1=6)+(A1=8)+(A1=10))<>0)
    format as red

    =(((A1=3)+(A1=4)+(A1=5)+(A1=13)+(A1>=14)*(A1<=17)+(A1=20))<>0)
    format as blue

  5. #5
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Conditional Formatting selective numbers

    Hi Glenn,

    Many thanks, this works fine, sorry my explanation which was very poor and I stupidly duplicated numbers.

    G

+ 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: 03-29-2015, 03:14 PM
  2. Conditional formatting to whole numbers
    By Ekko in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2015, 04:01 PM
  3. [SOLVED] Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers
    By ktbb0312 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-09-2013, 09:59 AM
  4. [SOLVED] Conditional Formatting for Whole Numbers
    By Majella in forum Excel General
    Replies: 15
    Last Post: 12-05-2012, 09:01 PM
  5. [SOLVED] Selective Formatting
    By SandGnats in forum Excel General
    Replies: 5
    Last Post: 04-05-2012, 11:04 AM
  6. Replies: 9
    Last Post: 03-26-2012, 02:16 PM
  7. Conditional Formatting on Whole Numbers?
    By excelgurudummy in forum Excel Formulas & Functions
    Replies: 58
    Last Post: 09-06-2005, 03:05 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