Conditional format cell in Calendar based on dates in another spreadsheet

Hi

I wanted to conditional format dates in calendar, based on Date of arrival/ appointment in another spread sheet.

Ideally would Like to format Date of starting & end of event in different colour & the continuum in a different colour. eg 2016 relates to Calendar2016

Currently using calendar from

https://www.vertex42.com/ExcelTempla...-calendar.html

(Using this Calendar as allows me to generate a new one every year)

Open to any suggestion for calendar or change data in spreadsheet

Many thanks

KS,

You may use this conditional rule

By highlight the all calendar date, (ie
=$B$5:$AF$10,$B$14:$AF$19,$B$23:$AF$28

if not it will highlight the month header.

Formula:

Please Login or Register  to view this content.

Thank you,

=SUMPRODUCT(--(B5>='2017'!$B$2:$B$7),--(B5<='2017'!$E$2:$E$7))

since the conditional format is start by B5, hence, B5 in the formula mean the current cell.

the >= and <= are mean to between

SUMPRODUCT is a Function that use as IF function in here since conditional formatting is not able to use array formula,

the -- change the result to number, and the excel assume 1 = TRUE and0 = FALSE

Hope it clarify.

PS:you may copy the formula in the cell other than B5 and go to formula evaluate to see how it work. (path to formula evaluate is written below)