Conditional Formatting - To color following Mon as Public Holiday if Public Holiday falls on Sun

[piyo27]

Hi,

I have a workbook with 3 worksheets - 1st worksheet shows a yearly calendar, 2nd worksheet shows a list of course schedule, i.e. course name, course duration, start and end date, 3rd worksheet shows Public Holiday of that year.

Help needed to set a rule using conditional formatting to color the cell of following Mon on the yearly calendar if Public Holiday falls on Sun?

Rule to color cell for Public Holiday:=COUNTIF('Public Holiday'!$B$2:$B$12,B$4)>0
Rule to color cell for Weekend:=COUNTIF('Public Holiday'!$B$2:$B$12,B$4)>0

Thanks...

 

[oldbrewer]

presumably you have a list of dates of public holidays in one column, in adjacent column put mondays date if a sunday, otherwise put the original date

now apply cond formatting on all course schedule sheets - if vlookup any date returns a different date color the cell

 

[FormR]

Help needed to set a rule using conditional formatting to color the cell of following Mon on the yearly calendar if Public Holiday falls on Sun?

Hi, you could also try this small amendment to your current rule.

Rule to color cell for Public Holiday:=COUNTIF('Public Holiday'!$B$2:$B$12,B$4-1)>0

 

[piyo27]

Hi, you could also try this small amendment to your current rule.

Try this and all Public Holidays shifted 1 more day i.e. Public Holiday falls on Mon and Tue cell will be colored instead.

 

[FormR]

Try this and all Public Holidays shifted

Hi, how about this further amendment then?

=AND(COUNTIF('Public Holiday'!$B$2:$B$12,B$4-1)>0,WEEKDAY(B$4)=2)

 

[piyo27]

Hi, how about this further amendment then?

=AND(COUNTIF('Public Holiday'!$B$2:$B$12,B$4-1)>0,WEEKDAY(B$4)=2)

Tried the above formula and all the color for Public Holiday disappeared. Btw, I managed to resolve this issue. Thanks so much for your help

 

[piyo27]

Thanks all for your help. Managed to resolve the issue

 

[FormR]

Tried the above formula and all the color for Public Holiday disappeared

Oh, I thought you were after a new rule - to combine them you'd do something like.

=OR(AND(COUNTIF('Public Holiday'!$B$2:$B$12,B$4-1)>0,WEEKDAY(B$4)=2),COUNTIF('Public Holiday'!$B$2:$B$12,B$4)>0)

Btw, I managed to resolve this issue. Thanks so much for your help

Great, good to hear feel free to post what you ended up using so other viewers of the thread can potentially benefit.

 

[piyo27]

Oh, I thought you were after a new rule - to combine them you'd do something like.

=OR(AND(COUNTIF('Public Holiday'!$B$2:$B$12,B$4-1)>0,WEEKDAY(B$4)=2),COUNTIF('Public Holiday'!$B$2:$B$12,B$4)>0)

Great, good to hear feel free to post what you ended up using so other viewers of the thread can potentially benefit.

Oops I thought u meant replacing my existing code. Oh I tried the above formula and it works!

What I did previously was add 2 columns, i.e. C and D, in Public Holiday worksheet to reflect the day in column C and the the following Mon date in column D. After which, include a formula in conditional formatting to pick up date in column D and reflect the color in the cell accordingly. It's kind of a workaround. You solution is more directly and I have adopted that. Thanks so much for your help