Conditional Formatting BASED on range of cells

[investntrade]

Hello,

I have a list of dates in a column (say A1:A20)
I have list of holidays in another column (say B1:B10).

I want to highlight the cells of column A, if any of them match column B. In short, I want to highlight all the holidays in my master date list.

Conditional formatting allows formatting only on one single cell. Is there a way I can achieve conditional formatting for the above (i.e. based on a range of cells containing holidays)?

Thanks

 

[Fluff]

Select A1:A20 & in CF select new rule > use a formula & use
=ISNUMBER(MATCH(A1,B$1:B$10,0))

 

[Norie]

Try this.

1. Select A1:A20.
2. Goto Format>Conditional formatting...
3. Select Use formula to determine cells to be formatted...
4. Enter this formula:
   
   
   =MATCH(A1, $B1:$B10, 0)​
   ​
5. Set formatting as required.
6. Press OK.

 

[investntrade]

Thanks @Norie and @Fluff for your prompt responses.
I should have mentioned it earlier that I am using Excel 2010..... I don't see that "Use Formula" option under conditional formatting.......have explored all available options under "Conditional Formatting"

Looks like it is not supported in that older version? Or may I missing something.

 

[Fluff]

You need to select New Rule, then Use a formula

 

[investntrade]

Thanks a lot, it works!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Norie]

Oops, forgot the New Rule... bit - suppose that's kind of important.