# 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!

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

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.

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

Ace-Thanks so much!

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

Thanks bebo for your assistance as well!

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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