+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting: highlight if cell contents do not exist in range

  1. #1
    Registered User
    Join Date
    10-05-2011
    Location
    NYC
    MS-Off Ver
    Excel 2010 Professional Plus
    Posts
    44

    Conditional Formatting: highlight if cell contents do not exist in range

    My spreadsheet contains 5,000+ rows of text. My amounts are located in column I. I want to highlight those numbers if the corresponding account, located on the same row in column C, is not contained within a range of cells located on another sheet. My current conditional formatting formula is

    =COUNTIF(Range,"<>$C:$C")

    I have also tried

    =COUNTIF(Range,$C:$C)<>1

    Each amount will only have one account associated with it in column C, but the row location of the amounts shifts from month to month. Because of this, and due to the length of the spreadsheet, I was anticipating having to select all amounts each month (F5 -> Special...) and then insert this CF formula.

    Any help would be greatly appreciated.

  2. #2
    Registered User
    Join Date
    10-05-2011
    Location
    NYC
    MS-Off Ver
    Excel 2010 Professional Plus
    Posts
    44

    Re: Conditional Formatting: highlight if cell contents do not exist in range

    The following CF formula works but it also is highlighting any amounts in column I that are totals. These total amounts have a blank cell in column C where the corresponding account would normally be.

    =COUNTIF(Range,$C5)<>1

    I need to amend this formula to also exclude blank cells from being highlighted. I have tried adding a blank cell to my range.
    Last edited by Bjordion; 03-28-2012 at 06:23 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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