Format Cells Based on Given Input

[Admin]

Hi Rajesh,

Welcome to ExcelFox !!

It can be done through conditional formatting.

Define ranges:

EndDate

Refers to: =Sheet1!$G$4:$G$8

Holidays

Refers to: =Sheet1!$N$4:$N$23

SalesPersons

Refers to: =Sheet1!$C$4:$C$8

StartDate

Refers to: =Sheet1!$F$4:$F$8


Select C4:EY9 on sheet2

In Conditional formatting

Rule1:

=OR(WEEKDAY(C$3,2)>5,ISNUMBER(MATCH(C$3,Holidays,0 )))

Rule2:


=AND(ISNUMBER(MATCH($B4,SalesPersons,0)),C$3>=INDE X(StartDate,MATCH($B4,SalesPersons,0)),C$3<=INDEX( EndDate,MATCH($B4,SalesPersons,0)),WEEKDAY(C$3,2)< 6,ISNA(MATCH(C$3,Holidays,0)))


Format the cells accordingly.

Note: I have define a range for Holidays.

PFA.

If you prefer VBA rather than CF, let me know.