+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting - multiple criteria

  1. #1
    Forum Contributor
    Join Date
    06-28-2004
    MS-Off Ver
    Home/Office 2016
    Posts
    259

    Conditional Formatting - multiple criteria

    Search COL A - if an "O" is found, change all of the values in COL C to RED
    if they match the same value as COL C of the "O" record

    Example:
    x - Jim - 5
    O - Mike - 4 All 4's in COL C would turn red (Mike & Hank)
    x - Pete - 3
    x - Hank - 4

    Attached example.
    Attached Files Attached Files
    Last edited by x65140; 12-14-2013 at 12:17 PM. Reason: followup

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Conditional Formatting - multiple criteria

    Place the cursor in C3 and extend the selection upto C10. Now C3 willl be the activecell in your selection.

    Press Alt+O+D>>New Rule>>Formula To determine>> use the below formula

    =VLOOKUP("O",A:C,3,FALSE)=C3


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,463

    Re: Conditional Formatting - multiple criteria

    Select cells C3 to C10, then click on Conditional Formatting | New Rule | Use a Formula ...

    Then put this formula in the box:

    =C3=VLOOKUP("O",$A$3:$C$10,3,0)

    then click on the Format button, and in the Font tab use the drop-down for Color and choose red. Then OK your way out.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    06-28-2004
    MS-Off Ver
    Home/Office 2016
    Posts
    259

    Re: Conditional Formatting - multiple criteria

    Awesome! Thanks

  5. #5
    Forum Contributor
    Join Date
    06-28-2004
    MS-Off Ver
    Home/Office 2016
    Posts
    259

    Re: Conditional Formatting - multiple criteria

    Thx for your help. One follow up question to my original....

    What if COL A had multiple "O" - how can that work?
    In this case....the MARK/TINA 9's would be highlighted and also the MIKE/JUDY 6's would be highlighted

    x - Mike - 6
    x - John - 5
    o - Mark - 9
    x - Stan - 3
    x - Tina - 9
    o - Judy - 6
    x - Jeff - 1
    x - Bill - 3

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,463

    Re: Conditional Formatting - multiple criteria

    See attached file.

    I've put this array* formula in E1 to find the largest value in column C where column A contains "o":

    =LARGE(IF($A$1:$A$8="o",$C$1:$C$8),ROWS($1:1))

    and then copied this into E2 to find the next largest value. You can copy this down further if you have other values, but delete any #NUM errors that might occur if you copy it too far.

    Then I highlighted cells A1:C8 and clicked on Conditional Formatting | New Rule | Use a formula, and then put this formula in the box:

    =ISNUMBER(MATCH($C1,$E:$E,0))

    and then clicked the Format button and chose a Red fill. You can see all 3 adjacent cells are highlighted, as requested.

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ 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 - multiple criteria
    By bobbie_ch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-05-2013, 08:46 AM
  2. Replies: 5
    Last Post: 12-28-2012, 02:06 PM
  3. Conditional formatting multiple criteria
    By imcurious in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-28-2011, 05:36 AM
  4. Conditional formatting on multiple criteria
    By floribunda in forum Excel General
    Replies: 2
    Last Post: 07-11-2011, 10:25 AM
  5. conditional formatting, multiple criteria
    By Lara in forum Excel General
    Replies: 2
    Last Post: 06-27-2006, 01:15 PM

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