Conditional Formatting multiple rows in excel

Question

I am stuck on a conditional formatting question. So I have a dynamic calendar template in which you can change month and week etc. The dates only range from column E to column X. It looks like this:

The 11th row is the date row, and the orange cells are dates which are holidays. The conditional formatting to color those cells is:

=COUNTIF(holiday,E$11)>0

where holiday is a named range containing the list of holidays. What I would like to do is color the rows below as well when the date is a holiday. So in this case, if 13th may is a holiday, I12:I17 should also be colored orange.

Any help will be appreciated. Thanks

Edit: As @SRA suggested, if I select the whole E11:X17, it solves my problem halfway as posted:

What I also wanted was to get rid of the text which is coming through conditional formatting, not the text of the date which is the 11th row, but the text of the below rows if its a holiday.

Thanks a lot

SRA SRA · Accepted Answer · 2020-05-11T08:34:58

You could select the entire range I12:I17 and then apply the conditional formatting

EDIT:

Do the following 2 steps

1) Selec I11 and apply the below formatting

(ONLY BACKGROUND IS ORANGE)

2) Select I12 : I17 and apply the below formatting

(BAKGROUND and FONT both orange)

Conditional Formatting multiple rows in excel

1 Answers