+ Reply to Thread
Results 1 to 10 of 10

How to apply conditional formatting if length of cell is more than 16

  1. #1
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    191

    How to apply conditional formatting if length of cell is more than 16

    Dear Friends,

    PFA

    How to apply conditional formatting if length of cell is equal to or more than 16? (refer cells A20, A21).
    What could be the reason for the failure ?

    Please assist.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,184

    Re: How to apply conditional formatting if length of cell is more than 16

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in CF
    does not working?
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    191

    Re: How to apply conditional formatting if length of cell is more than 16

    Friends,

    In the attachment, in row 20, 21 the conditional formatting is failing since the length of the cells is 16.
    If we remove the last digit in row 20, it works fine.

    Please assist.

  4. #4
    Valued Forum Contributor KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,184

    Re: How to apply conditional formatting if length of cell is more than 16

    Check attached file. Everything is correct LEN(X)>=16 highlighted cell in yellow.
    You have to decide what formatting is more important, duplicates (blue) or length (yellow) or rebuild formulas in CF to catch both issues.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,976

    Re: How to apply conditional formatting if length of cell is more than 16

    Perhaps you did not set the "applies to" range correctly?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    191

    Re: How to apply conditional formatting if length of cell is more than 16

    Sorry for the confusion.

    Actual my criteria for conditional formatting is not cell having length >=16.

    My criteria is for "Duplicates".

    So if we see row 20 and 21, even though the data is different, still they are highlighted.

    Please assist.
    Last edited by lalaarif1; 03-30-2019 at 12:45 AM.

  7. #7
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    191

    Re: How to apply conditional formatting if length of cell is more than 16

    Dear Kokosek,

    I am unable to open your attachment... Error pops up "file could not be found .. Check spelling of file name and verify that the file location is correct"

    Can you assist ?
    Last edited by lalaarif1; 03-30-2019 at 12:48 AM.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,684

    Re: How to apply conditional formatting if length of cell is more than 16

    Try using the following formula as a conditional formatting rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Credit is given to Zack Barresse who posted the formula from which the above is based in May of 2008 on another forum.
    The 25 will need to be changed to match the number of rows to which conditional formatting will be applied.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    191

    Re: How to apply conditional formatting if length of cell is more than 16

    Thanks a lot...
    You guys made my day....

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,684

    Re: How to apply conditional formatting if length of cell is more than 16

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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