Hello. Today i downloaded a calendar template (http://office.microsoft.com/en-gb/te...402.aspx?CTT=1) and it's coded so that when you enter a date in certain column it highlights the date on calender in blue, could anyone help me with a formula so it turns dates on calender into other color when i type in different column ?

Thanks

Hi,
try this (sorry for any translation mistakes, I use a Swedish Excel version).

Put some dates in column AE (for example). Lets say you reserve AE2:AE99 for this. Enter 1st of June 2011 (in your date format) in cell AE2 (just for test purpose later)

Mark cells C12:Y44. Select menu "Start / Conditional formatting / Create new rule". Select the last alternative "Determine what cells should be formatted using a formula". Enter this formula:
=IF(VLOOKUP(C12,$AE$2:$AE$99,1,FALSE())=C12,TRUE() )
and select an ugly colour to highlight your special dates.

Press OK and now you should see 1st of June highlighted

Kind regards
Bjorn

Unfortunately it didn't work, i get an error when i type the formula saying the formula contains an error :P
Perhaps there's a typo in your formula or did I do something wrong.
Thanks for the reply.

Did you successfully find Conditional Formatting?

If the cell where you want to type the date to highlight is A1, and the cell that is the upper left corner of the cells that contain dates is C5, then select the range (C5 through whatever) and use this formula in the Conditional Formatting dialog box:

=C5=$A$1

Hi, one problem could be language specific settings. Can you try to copy any of the other (already existing) formulas for conditional formating,m while standing in a date cell and paste it into the forum. Preferrably choose a formula that has this VLOOKUP function (I can't describe more accurate at the moment, no Excel on this computer).

Kind regards
Bjorn

Thanks for the help, I didn't notice I can edit formulas in conditional formatting, so when I was looking to copy the formula for you I just tweaked it a bit and it worked.

Thanks !

I should have looked at the template before posting (I would have realized there is a column to enter dates, not just a cell), but here is the formula, just a simple VLOOKUP is all that is needed. Assuming the calendar range begins with C5, and the dates will be entered in A1:A30...
=VLOOKUP(C5,$A$1:$A$40,1,0)