Results 1 to 8 of 8

Thread: Conditional Format query

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Format query

    Is it possible to highlight cells based on another cell. That is a bit of an oversimplification. My table looks something like this:

    Choice 1 Choice 2 Choice 3 Choice 4 Choice 5 Choice 6 Option W Option X Option Y Option Z
    History Spanish Media RS PE Art History Spanish Art RS

    If the entries under Options w to Z match choice 1 to 4 I want to highlight in green, if options W to Z match entries under Choice 5 and 6 I want to highlight in yellow. Is there any way this can be done. So the table should look like:

    Choice 1 Choice 2 Choice 3 Choice 4 Choice 5 Choice 6 Option W Option X Option Y Option Z
    History Spanish Media RS PE Art History Spanish Art RS

    As always, any help is very much appreciated.

    Thanks

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,151
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Format query

    Your example data

    How many rows does this cover? Looks like 3 rows with numbers and letters on the row 2 but cant tell cos they're not in boxes/cells.

  3. #3
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Conditional Format query

    This would obviously require to separate conditions:

    For the first condition (green):
    Code:
    =G2=OFFSET(G2,0,-6)
    For the second condition (yellow):
    Code:
    =OR(G2=$E2,G2=$F2)
    Tested on the data you provided (assuming you have headers)

    I assumed that the first condition would only be true if the first option matches with the first choice, the second with the second.. etc. If this is not the case, you can resort to the second condition I listed:
    Code:
    =OR(G2=$A2,G2=$B2,G2=$C2,G2=$D2)
    Last edited by Tim_Excel_; Mar 7th, 2019 at 06:38 AM.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,151
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Format query

    I'm assuming the cells to highlight are on row 2

    Select the range of cells to highlight, e.g. G2:J2

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

    =MATCH(G2,$A2:$F2,0)<=4
    format as green font

    =MATCH(G2,$A2:$F2,0)>=5
    format as yellow font

  5. #5
    Board Regular
    Join Date
    Nov 2014
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Format query

    Quote Originally Posted by Special-K99 View Post
    Your example data

    How many rows does this cover? Looks like 3 rows with numbers and letters on the row 2 but cant tell cos they're not in boxes/cells.
    Sorry should have added the grid lines there were 2 rows the header row and a sample row of data. Tim_Excel_'s solution worked great but thank you for taking the time to post.

  6. #6
    Board Regular
    Join Date
    Nov 2014
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Format query

    Quote Originally Posted by Special-K99 View Post
    I'm assuming the cells to highlight are on row 2

    Select the range of cells to highlight, e.g. G2:J2

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

    =MATCH(G2,$A2:$F2,0)<=4
    format as green font

    =MATCH(G2,$A2:$F2,0)>=5
    format as yellow font
    That works as well thanks Special-K99

  7. #7
    Board Regular
    Join Date
    Nov 2014
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Format query

    And thanks to you as well Tim_Excel_

  8. #8
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Conditional Format query

    Glad we could help
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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