+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Question Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

    Hi all,

    Sorry if this is in the wrong place, but I think it fits here as it's a function. I tried the search on all forums but couldn't easily find an answer to my dilemma.

    I have a sheet where I'm searching for duplicate values in Column A. My conditional formatting rule is as follows:
    Example1.png

    However, if I ever insert, copy or delete rows, the "Applies to" data changes, example here:
    Example2.png

    This causes some rows to be excluded from the function somehow.

    This is an ongoing spreadsheet I use at work and make weekly edits to. Is there a way I can have it so it will always look for duplicates in the whole column no matter what? (I want the $A:$A to always be the case no matter what happens to the sheet)

    Thanks in advance!!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

    Instead of using "Duplicate Values," use a formula =countif(A:A,A1) > 1
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Re: Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

    Thanks for your reply, but unless I'm doing something wrong, this won't apply the conditional formatting will it? I can paste this formula into a different column and all it seems to do is report "TRUE" or "FALSE" This isn't what I'm looking for.

    Edit:
    Here is an example of the results I achieve using the duplicate value conditional formatting. I intend to keep it this way:
    Example3.png
    Last edited by TrueFangz; 03-28-2018 at 04:00 PM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

    Select Conditional Formatting.

    Select Use a formula to determine which cells to format.

    Type in the formula as indicated in the picture.
    Attached Images Attached Images

  5. #5
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Re: Conditional Formatting Rules Change when Inserting, Deleting [finding duplicates]

    Got it, thanks!!

+ 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: 4
    Last Post: 03-16-2018, 01:08 AM
  2. Replies: 6
    Last Post: 06-19-2017, 02:23 PM
  3. Finding Duplicates via Conditional Formatting
    By MacroLearner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2015, 04:23 AM
  4. Inserting/Deleting Rows Causes Issues with Conditional Formatting Macro
    By lashellr in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2014, 01:25 PM
  5. Replies: 1
    Last Post: 08-31-2014, 03:38 PM
  6. Replies: 2
    Last Post: 06-08-2012, 05:47 AM
  7. Conditional Formatting & Finding Duplicates
    By mluetkem in forum Excel General
    Replies: 4
    Last Post: 01-17-2010, 10:48 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