Conditional Formatting the alternate rows with Criteria

thespardian

Board Regular
Joined
Aug 31, 2012
Messages
119
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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.
Question.png

https://www.dropbox.com/s/4ys79pbmixi1o3b/Question.png?dl=0


Question.png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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"..." ?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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-board/508133-attachments.html

Or as you've done before with a link to a spreadsheet uploaded to a storage site.
 
Upvote 0
How about
=AND($C4="Dinner",D4<>"",LEN(D4)-LEN(SUBSTITUTE(D4,"D",""))<>LEN(D4))
 
Upvote 0
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:
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top