Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Conditional Formatting Question

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting Question

    I need some help with conditional formatting. I have three columns (C2:E31) with data in them. Column B also has data in it. When the value in column B is changed and the values of Columns C, D and E do not match what B is changed to, I want C, D and E to change color. Can someone please assist?

    Thank you in advance!

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,709
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Conditional Formatting Question

    Try

    Select C2:E31
    In CF use the formula option

    Insert this formula
    =AND($B2<>"",COUNTIF($C2:$E2,$B2)=0)

    Format button and pick the format you want

    Hope this helps

    M.
    Last edited by Marcelo Branco; Apr 23rd, 2019 at 11:48 AM.

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Question

    Thanks for the response. That didn't quite work.

    For example, in one row, Column B (the control column) has A in it, column C has B, column D has A and column E has B. It is not highlighting C or D.

    But, in another row, Column B has A in it, column C, D and E have B and it is highlighting all the cells.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,709
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Conditional Formatting Question

    I think misunderstood your question

    Is this what you want?

    B
    C
    D
    E
    1
    2
    A
    B
    A
    B
    3
    C
    B
    B
    A
    4
    C
    B
    C
    A


    If so, select C3:E31 and use this formula in CF
    =AND($B2<>"",C2<>$B2)

    M.
    Last edited by Marcelo Branco; Apr 23rd, 2019 at 12:18 PM.

  5. #5
    New Member
    Join Date
    Jul 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Question

    That did it! Thank you so much.

    Do I change the <> to = if I want it to highlight a different color if it matches?

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,709
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Conditional Formatting Question

    Yes.

    =AND($B2<>"",C2=$B2)
    Fill --> Green (for example)

    M.

  7. #7
    New Member
    Join Date
    Jul 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Question

    That worked. Thank you!

    If I want to add a count if formula in there, what would be the best way to do that? In each column (C, D and E), I want to count the number of cells that match Column B. I would need to know the total for number that match for each column.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,709
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Conditional Formatting Question

    Where do you want to add the COUNTIF(...) formulas?
    In CF formulas? If so, what is the purpose?
    In short: what exactly are you trying to do with the results of COUNTIF(...) formulas?

    M.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,709
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Conditional Formatting Question

    Quote Originally Posted by jmattingly85 View Post
    I would need to know the total for number that match for each column.
    See if this example helps


    B
    C
    D
    E
    1
    2
    A
    B
    A
    B
    3
    A
    B
    A
    A
    4
    B
    B
    C
    A
    5
    6
    7
    Formulas-->
    1
    2
    1


    Formula in B7 copied across
    =SUMPRODUCT(--(C2:C4=$B2:$B4))

    M.
    Last edited by Marcelo Branco; Apr 23rd, 2019 at 01:04 PM.

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
  •