Conditional Formatting Error

[slc]

Appreciate it if some experts could tell me what wrong with m
conditional formatting (CF) formula. In Col A, I have dates (se
example below). I want the current week to be highlighted with
different color.

My current CF for A1 cell is something like this:-
=AND(WEEKNUM(A1)=WEEKNUM(NOW()),1).

However, I encounter this error and Excel would not allow me t
proceed.
"You may not use references to other worksheets or workbooks fo
Conditional Formatting criteria".

I am not making any references to other worksheets or workbooks.

Thanks.

Col A
[1] 3-May-04
[2] 10-May-04 <-- this cell should be highlighted in diff col.
[3] 17-May-04
[4] 24-May-04
[5] 31-May-04
[6] 7-Jun-04
[7] 14-Jun-04
[8] 21-Jun-04
....


:

 

[JE McGimpsey]

You *are* making a reference to other workbooks - WEEKNUM() is a
function in the Analysis ToolPak Add-in, which is another workbook.

Try this (modified from Chip Pearson's site:

http://cpearson.com/excel/weeknum.html


=(INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5) +
WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)) =
(INT((TODAY()-DATE(YEAR(TODAY()+4-WEEKDAY(TODAY()+6)),1,5) +
WEEKDAY(DATE(YEAR(TODAY()+4-WEEKDAY(TODAY()+6)),1,3)))/7))

 

[Stephen Bye]

It is because the WEEKNUM function is in another workbook (the Analysis tool
pack add-in).

Try this instead, which checks to see if A1 is between the start and end
dates of the current week:
=AND(A1>=(TODAY()-WEEKDAY(TODAY())+1),A1<=(TODAY()-WEEKDAY(TODAY())+7))