+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting when two cells in a row do not equal AND one is not blank

  1. #1
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Conditional Formatting when two cells in a row do not equal AND one is not blank

    I am trying to figure out how to highlight a row or two cells in a row when they do not equal each other. However, I only want them to be highlighted if both cells have a value in them. In the attached example, I do not want rows 72 or 76 to be highlighted since there are not values in column D (estimated costs) or F (Total Committed) but I do want row 81 to highlight Cell D81 and F81 since they are not the same value. Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,166

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    i have used
    =AND( $D6<>0, $F6<>0, $D6<>$F6)
    as a cond frmt rule
    does that do what you need?

    I Selected all the columns and then highlight yellow

    This highlights the entire row

    But you can change to just the cells by changing the selection
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    This weeded out the cells that didn't have values in them but still is not highlighting the ones that don't have the same value in column D and F.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,166

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    can you give examples of the row - you said
    I do not want rows 72 or 76 to be highlighted since there are not values in column D (estimated costs) or F (Total Committed) but I do want row 81 to highlight Cell D81 and F81
    So I assumed all other rows were OK and did not want highlighting

    If F is a zero and D has a value - is that highlighted
    if so , its most of the rows

    So is it ONLY cells that are
    A) Highlight ONLY if D & F are Different in value, even if 1 cell is zero
    b) do NOT highlight IF D & F are the same, BUT
    c) do NOT highlight if both D & F are ZERO
    Last edited by etaf; 05-11-2021 at 04:16 PM.

  5. #5
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    No, I just want the rows highlighted where there are values (not blank/0) in both cells D & F but those values are not the same. Usually I have multiples rows with values in column D but nothing in column F. Those do not need to be highlighted and the ones with nothing in either column don't get highlighted either. I am only trying to point out when there are two values and they are not the same value in columns D & F.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,166

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    isn't that what I did?
    Row 81 is highlighted

    What other ROW should be highlighted and WHY
    IF I am missing something here - SORRY

  7. #7
    Registered User
    Join Date
    07-08-2016
    Location
    Pensacola, FL
    MS-Off Ver
    2016
    Posts
    20

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    I think our disconnect is in a) of your response above. I want it to highlight only if D & F are different value but NOT if cell in column F is zero. I am sorry I am being so confusing!

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,166

    Re: Conditional Formatting when two cells in a row do not equal AND one is not blank

    thats what I thought I had done
    =AND( $D6<>0, $F6<>0, $D6<>$F6)

    Just noticed its a pivot table and that changes the conditional format range everytime you change or filter the Pivot table

    Column D & F does not have blanks - only zeros and numbers
    so

    =AND( $D6<>0, $F6<>0, $D6<>$F6)

    D6 must not = zero AND F6 must not = zero
    so that covers the do not highlight zeros cells

    then we have another AND
    $D6<>$F6

    SO that should do it

    BUT i'm not sure how a Pivot Table reacts to conditional formatting and that may be the issue

    Meanwhile in the sample sheet - What ROWS other than 81 should be highlighted ?

+ 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: 1
    Last Post: 10-12-2018, 04:23 PM
  2. Replies: 5
    Last Post: 12-08-2016, 04:17 AM
  3. [SOLVED] Conditional Formatting - If three cells are equal
    By per11 in forum Excel General
    Replies: 8
    Last Post: 08-23-2016, 02:09 PM
  4. [SOLVED] Conditional Formatting - If three cells are equal but not 0
    By per11 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-23-2016, 01:53 PM
  5. Replies: 5
    Last Post: 06-25-2015, 12:05 PM
  6. [SOLVED] Conditional formatting if equal or greater than average in the range of cells
    By Abu Abdurakhim in forum Excel General
    Replies: 7
    Last Post: 05-28-2013, 10:01 AM
  7. If cell is blank then equal (not using conditional formatting)
    By StuartMansfield in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2012, 07:19 AM

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