+ Reply to Thread
Results 1 to 5 of 5

Some help with conditional formatting

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Some help with conditional formatting

    I currently have a spreadsheet with column N listing usernames. Column N already has a formula which is =CONCATENATE(LEFT(B2,1),D:D). I would like to add to this formula some way to highlight that cell red if duplicate is found.

    The problem is that the formula is filled down to 8459437239 rows. When I use the conditional formatting tool, it highlights those rows which have a formula but are empty. How can I keep them from getting highlighted until actual data is entered?

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Some help with conditional formatting

    Use
    Rule 1: format blanks--no format
    Rule 1: format duplicates -- your format
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Some help with conditional formatting

    Not sure what your forumla is, but try changing it to something like this:
    =and(not(isblank(N2)),<your forumla>)

    That should return true if your fomula is true and the cell is not blank.

    Pauley

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,797

    Re: Some help with conditional formatting

    Quote Originally Posted by mnhim001 View Post
    I How can I keep them from getting highlighted until actual data is entered?
    Select the existing range of on empty rows
    Go to the Insert ribbon and click Table - Rename the table or not - Create your CF as usual
    Each time you add a row, CF will follow

  5. #5
    Registered User
    Join Date
    07-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Some help with conditional formatting

    Thanks Pepe, creating the table works without me having to fill down all the time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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