+ Reply to Thread
Results 1 to 16 of 16

Conditional Formatting

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Conditional Formatting

    Hello All -

    I have tried for a couple of days now and can't figure out his conditional formatting.

    I have a table with dates and other information my goal is to sort the table from earliest date to latest and highlight dates and if there is a duplicate date.. highlight the very last one for example below

    1/1/2019 - highlight
    1/2/2019 - highlight
    1/5/2019
    1/5/2019 - highlight

    Thank you to all!
    Last edited by erwhe5tr; 10-08-2019 at 10:51 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting

    Why is 1/5/2016 highlighted? It has no duplicate, the year is 2016. In fact there are no duplicates in your example data.
    Why is 1/1/2019 highlighted? It's not the last date, the last date numerically when sorted is 1/5/2019 but that's not highlighted. Which begs the question...
    ...are we highlighting the very last date or the very last duplicate?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    Hi sorry... it has been adjusted shouldve been 2019 fat fingered it...

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting

    Hello and welcome to the forum.

    Assuming your dates are in column A starting in row 2, try this:

    Highlight A2:A100 (or however far your data might go) > Conditional Formatting > New Rule > Use a formula
    =OR(COUNTIF(A:A,A2)=1,COUNTIF(A$2:A2,A2)>1)
    Format: Fill color of your choice > OK > OK

    You can sort however you'd like and the Conditional Formatting will still work.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting

    Why is 1/1/2019 highlighted? It's not a duplicate.
    Same with 1/2/2019 ??

  6. #6
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    the goal is to highlight the unique dates and the last date in the sequence of duplicate dates

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting

    I'm glad I asked (no mention of unique dates until now).

    Maybe 63falcondude's solution will still work?

  8. #8
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    it was very close it highlighted all the unique values but the duplicate portion... it highlighted every single one but the first duplicate

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Conditional Formatting

    OR to select the last bduplicate as per your example
    =a2<>a3 in a2


    to select the first

    =a2<>a1 in a2

  10. #10
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    can u put it all in one formula?

  11. #11
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting

    You can use this as your CF rule:

    =COUNTIF($A:$A,$A2)=COUNTIF($A$2:$A2,$A2)

    Change the "Applies to" area from A2:A100 to A2:Z100 (or however far you need to highlight).
    Last edited by 63falcondude; 10-11-2019 at 11:34 AM.

  12. #12
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Conditional Formatting

    yes its to give you the choice of
    1/1/2019 - highlight
    1/2/2019 - highlight
    1/5/2019
    1/5/2019 - highlight

    =a2<>a3 in a2



    or if you want the first

    1/1/2019 - highlight
    1/2/2019 - highlight
    1/5/2019 - highlight
    1/5/2019

    =a2<>a1 in a2


    it has to be sorted

  13. #13
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    thank you so much!!!!!!!!!!!!!!!!!!!!!

  14. #14
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting

    Glad we could help. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  15. #15
    Registered User
    Join Date
    10-08-2019
    Location
    detroit, usa
    MS-Off Ver
    2019
    Posts
    7

    Re: Conditional Formatting

    Hi Sorry.... to throw a wrench in this.. what if i want to highlight the entire row?

  16. #16
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting

    Whoops... edited my original response instead of posting a new one...

    You can use this formula:

    =COUNTIF($A:$A,$A2)=COUNTIF($A$2:$A2,$A2)

    Change the Applies to area from A2:A100 to A2:Z100 (or however far you want to go).

+ 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] Conditional Formatting based on another cells Conditional Formatting
    By chriskay in forum Excel General
    Replies: 4
    Last Post: 08-22-2019, 05:33 AM
  2. [SOLVED] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 PM

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