Conditional Format based on two column criteria

[tammyl]

Hi,

I'm trying to conditionally format a calendar cell (background colour) based on criteria from two columns.

Date, Type
1/1/10, 1
1/1/10, B
6/1/,10, B
10/1/10, B
20/1/10, A

1 = Holiday
B = Birthday
A = Anniversay

January 2010MTuWThFSaSu 12345678910111213141516171819202122232425262728293031

H11, being the first cell in the calendar.

I've tried the following but can't get the reference to work for my 'Type' column. The following formula colours the background if the calendar cell date is within the Dates named range.

=MATCH(H11,HList_Dates,0)

Basically, i want it to colour the background if

If Type =B and calendar cell date = Date then colour cell - Pink
If Type = A and calendar cell date = Date then colour cell - Purple
If Type = 1 and calendar cell date = Date then colour cell - Blue

Hope someone can assist.

Thanks tammyl

[Bob Phillips]

Try

=AND(MATCH(H11,HList_Dates,0),I11=1)

etc.

[tammyl]

Hi xld,

Apologies i don't think i explained cell locations clearly.

Date is in column A, begin row 3 (named range called HList_Dates)
Type is in column B, begin row 3 (named range called Type)

1st day of Mini Calendar starts in H11

If MiniCal date(H11) = date in HList_Dates(column A) AND Type = "B" then Background colour = pink.

Tried this:
=AND(MATCH(H11,HList_Dates,0),MATCH("B",Type,0))
but all cells with a date in HList_Dates turned pink not just the ones with a Type=B in column B

then tried this:
=AND(MATCH(H11,HList_Dates,0),MATCH("B",$B3,0))
referring to column B, row 3 is the first record in the range 'Type'
my understanding with conditional formatting is that the $B3 would increment to $B4, etc. (Could be wrong!)

That didn't work either. Some cells blue, some pink but not the right ones!!

Arrgh, so close

Cheers tammyl

[Bob Phillips]

Then shouldn't you be using

=AND(MATCH(A3,HList_Dates,0),B3="B")