Results 1 to 10 of 10

Thread: Conditional Formatting the alternate rows with Criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting the alternate rows with Criteria

    Hi there!

    I have shared the screen shot in the link below:-

    In the rows "Dinner" (not the rows "Breakfast"), if there is any letter other than "D". The cell/cells should be highlighted.

    Any help would be highly appreciated.

    https://www.dropbox.com/s/4ys79pbmix...stion.png?dl=0



  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,259
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    This needs to be clarified.

    In F9 you have DDD
    in J9 you have DD

    Is this considered to be "any letter other than D" since neither "DDD" nor "DD" are "...other than "D"..." ?

  3. #3
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    Thanks a lot for your input. if "D" is single or repeated in the cell--- like "DD" or "DDD" then its OK. But If there is any other letter then it should be highlighted.

    So F9 and J9 should not be highlighted
    Last edited by thespardian; Oct 18th, 2019 at 07:44 AM.

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,259
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    Try this (untested), not sure what will happen with weekends

    Select the range to highlight, e.g. D4:AH1000

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =AND($C4="Dinner",LEN(D4)-LEN(SUBSTITUTE(D4,"D",""))=LEN(D4))
    format as required

  5. #5
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    I spend two days with your formula but could not figure out where am i wrong? The above mentioned formula didn't work for me. I am using the following formula. It works for me but when it comes to "DD" or "DDD" in any cell it highlight the cell which is not acceptable in my case

    =AND($C4="Dinner",D4<>"",D4<>"D")

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,259
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    I can't test on an image, need the original file.

    Post a small sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.

    You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

    https://www.mrexcel.com/forum/about-...tachments.html

    Or as you've done before with a link to a spreadsheet uploaded to a storage site.

  7. #7
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    Thanks for your guidance

    here is the link for file

    https://app.box.com/s/f8uoccspxhsedvtszujycbghcz9hn1x9

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    How about
    =AND($C4="Dinner",D4<>"",LEN(D4)-LEN(SUBSTITUTE(D4,"D",""))<>LEN(D4))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2012
    Posts
    45
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    Oh my good God!

    Thanks a lot for your input Fluff, works pretty well

    and special thanks to you Special-K99 for all your guidance and patience with me.

    You both give me the strength of knowledge for which i am really thankful to you. Stay blessed
    Last edited by thespardian; Oct 21st, 2019 at 08:14 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Conditional Formatting the alternate rows with Criteria

    Glad we could help & thanks for the feedback.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •