+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Between Two Columns

  1. #1
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Conditional Formatting Between Two Columns

    Hi Everyone,


    I have this conditional formatting the highlighted cell when detected multiple data and it works fine. But i wanted to add more conditioned that will only highlighted when the other column has a different data.

    I attach sample excel file for more details.

    Thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Conditional Formatting Between Two Columns

    Actually, I think the formula you need to use in teh 1st column is this...
    =COUNTIF($A$4:$A$19, A4)>1
    not this...
    =COUNTIF(A4:A19, A4)>1
    by the time you get to row 19, it has adjusted to this...
    =COUNTIF(A19:A34, A19)>1

    Regarding the next table, I dont see what you are trying to do?
    A/1 appears 2 times and so does C/1, so why is A/1 highlighted and C/1 is not?
    In fact, the only pairs that dont appear twice are E/2, A/2, A/3, B/2, D/2 and E/1

    H
    I
    J
    3
    GROUP
    LEVEL
    Count
    4
    A
    1
    2
    5
    B
    1
    2
    6
    C
    1
    2
    7
    D
    1
    2
    8
    E
    2
    1
    9
    F
    1
    2
    10
    A
    1
    2
    11
    A
    2
    1
    12
    A
    3
    1
    13
    B
    1
    2
    14
    B
    2
    1
    15
    C
    1
    2
    16
    D
    1
    2
    17
    D
    2
    1
    18
    E
    1
    1
    19
    F
    1
    2


    J4=COUNTIFS($H$4:$H$19,H4,$I$4:$I$19,I4)
    copied down

    And maybe that is the formula you are looking for anyway?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Conditional Formatting Between Two Columns

    Hi,

    yoy could try as conditional formatting rule:

    =(COUNTIF(A$4:A$19,A4)>1)*((COUNTIF(A$4:A$19,A4)>COUNTIFS(A$4:A$19,A4,B$4:B$19,B4)))


    Regards

    Edit:sorry FDibbins, I did not mean to overlap...
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Conditional Formatting Between Two Columns

    canapone, not a problem at all

    The more answers we have, the bigger the chance the OP will find 1 they can use and understand
    Besides, who's to say I read the question right in the 1st place? We have all see that different people interpret questions in different ways

  5. #5
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Conditional Formatting Between Two Columns

    Thanks FDibbins for the fast reply....

    I've noted your suggestion on the 1st column...

    The Group C and F were not highlighted because it has only a Level 1 and no other Level even it was appear twice. Other Group has been highlighted because it has other Level like 2 or 3.

    I want this conditional formatting to easily identify if such group has occur a different Level.

    Hoping you got my point...

  6. #6
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Conditional Formatting Between Two Columns

    Quote Originally Posted by canapone View Post
    Hi,

    yoy could try as conditional formatting rule:

    =(COUNTIF(A$4:A$19,A4)>1)*((COUNTIF(A$4:A$19,A4)>COUNTIFS(A$4:A$19,A4,B$4:B$19,B4)))


    Regards

    Edit:sorry FDibbins, I did not mean to overlap...

    Thank you so much... it works perfectly!!!

    Don't worry FDibbins.... you have the Reputation!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Conditional Formatting Between Two Columns

    OK, I think I understand. Use this as your rule...
    =COUNTIFS($H$4:$H$19,H4,$I$4:$I$19,">1")

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Conditional Formatting Between Two Columns

    Thanks for the rep

    Looks like my 2nd formula returns the same as canapone

+ 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. Conditional Formatting on two columns
    By Mr_Phil in forum Excel General
    Replies: 5
    Last Post: 09-04-2016, 03:10 AM
  2. conditional formatting three columns or more
    By awinchester in forum Excel General
    Replies: 7
    Last Post: 06-14-2016, 01:38 AM
  3. [SOLVED] Conditional Formatting with Two Columns
    By sombiatz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2014, 11:02 AM
  4. Conditional Formatting two columns
    By May10 in forum Excel General
    Replies: 1
    Last Post: 01-09-2014, 09:00 PM
  5. IF columns with Conditional Formatting
    By adfeddon in forum Excel General
    Replies: 3
    Last Post: 08-09-2013, 10:52 AM
  6. Conditional formatting two columns
    By Bupitz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2013, 03:54 PM
  7. Conditional Formatting columns
    By wally in forum Excel General
    Replies: 2
    Last Post: 02-14-2011, 09:06 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