# Question regarding conditional format question that covers two range criteria

1. ## Question regarding conditional format question that covers two range criteria

Hello,

I'm in need of a conditional format formula that would turn a cell gray based on two conditions. My intention is to highlight a range from D3 through GG40 to have the condition formatting applicable for. The conditional format formula would then do the following:
First-The formula would need to identify any cells that have "H" in it from "D1:GG1" and then for those columns only apply the second condition, which is to look at column "GZ3" to "GZ40" and identify only those cells that have "DO" in them. As an end result example: if "D1" had an "H" in it and there was a "DO" in cells "GZ5" and "GZ6", cells "D5" & "D6" would turn gray, etc.

I tried using a formula like this: =AND(D1:GG1="H",\$GZ3:\$GZ34="DO") but it didn't work.

Any suggestions would be greatly appreciated!  Register To Reply

2. ## Re: Question regarding conditional format question that covers two range criteria

Do this..

- Select range (D3:GG40)
- IN CF rules use ``Please Login or Register  to view this content.``
format gray  Register To Reply

3. ## Re: Question regarding conditional format question that covers two range criteria

Try in D5:
If case sensitive (do<>DO,h<>H):
=ISNUMBER(FIND("H",D\$1)+FIND("DO",\$GZ5))
if not sensitive:
=ISNUMBER(SEARCH("H",D\$1)+SEARCH("DO",\$GZ5))
: above is applied to CF of D5 and copied to all range of D column, not typed in cell D5.  Register To Reply

4. ## Re: Question regarding conditional format question that covers two range criteria

Ace-Thanks so much!  Register To Reply

5. ## Re: Question regarding conditional format question that covers two range criteria

Thanks bebo for your assistance as well!  Register To Reply

6. ## Re: Question regarding conditional format question that covers two range criteria

If the text in D1 is "H" only and in GZ is "DO" only, use both ACE's and mine.
If D1 contains "H" (like "House","H"...) and GZ contains "DO" (like "DOIT","DO",...), my formula works, ACE's doesnot.  Register To Reply