+ Reply to Thread
Results 1 to 4 of 4

Highlight value in one column if its negative value is in another column.

  1. #1
    Registered User
    Join Date
    01-26-2022
    Location
    Wrightstown, New Jersey
    MS-Off Ver
    Windows 10 Enterprise, 1909
    Posts
    20

    Highlight value in one column if its negative value is in another column.

    I have two columns of dollar values - one is an invoice amount, and the other is the amount paid. I need to be able to see which invoice dollar amounts have a corresponding negative value. For my conditional formatting, I used =COUNTIF(($B2:$B400),-($A2:$A400)) but it works inconsistently. In other words, it highlights $161.47 because there is a $-161.47, but it doesn't highlight $239.72 even though there's a $-239.72.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,789

    Re: Highlight value in one column if its negative value is in another column.

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-26-2022
    Location
    Wrightstown, New Jersey
    MS-Off Ver
    Windows 10 Enterprise, 1909
    Posts
    20

    Re: Highlight value in one column if its negative value is in another column.

    Can you help me understand this? I don't get what the a1>0 means. What is a1 referencing?

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,789

    Re: Highlight value in one column if its negative value is in another column.

    It means the value in the cell must be greater than zero. Therefore, the condition requires that both the A column cell is not blank/zero AND there is found a negative matching value in column B.

+ 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] search & highlight the case sensitive text in one column that match a list on other column
    By Alexander2020 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-07-2020, 03:17 AM
  2. Programing a column so all numbers entered in column cells will be negative
    By clintrobbins in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2017, 10:14 PM
  3. Replies: 7
    Last Post: 06-18-2015, 02:23 AM
  4. Replies: 2
    Last Post: 08-04-2014, 08:57 AM
  5. [SOLVED] If a cell in column T is empty, use column U (but make it negative)??
    By dcappelletti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2014, 11:40 AM
  6. Replies: 4
    Last Post: 01-15-2013, 11:55 PM
  7. [SOLVED] Retrieve data from Column A based on a 1st negative value from Column B
    By edram95 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2012, 12:37 PM

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