How do I apply conditional formatting to a range of cells, not just one?

Question

Hi. I am trying to create an absence calendar for work.

I have created a drop down list of all the months in cell B3, and I have got a row of dates from 1st to 31st, from B5 to AF5.

I want it so when you select a month, the Saturday and Sundays are automatically shaded. I have highlighted the first Saturday, so from C6 to C9 and click CF. Clicked Use a formula to determine which cells to format, and put the formula =$B3=“December”

This works, but only C6 is shaded. How do I get all cells to match? In the applies to section, it shows =$C$6:$C$9

I’ve tried editing and figuring it out myself but whatever changes I make automatically revert to the above $C$6:$C$9

Please could someone direct me

Answer 1

Select B6:AF6

Formula:

=WEEKDAY(DATEVALUE($B$3&B$5),2)>5

Answer 2

Hello Liam,

Create a copy of excel sheet and follow these steps on a excel copy to prevent Loss of Data.

Select the range where you want the formatting to apply:

In your case, select the range from B6 to AF(whatever the last row is).

Go to the "Home" tab:

Click on the "Home" tab in the Excel ribbon.

Click on "Conditional Formatting":

Under the "Home" tab, find the "Conditional Formatting" option.

Choose "New Rule":

From the dropdown, select "New Rule."

Select "Use a formula to determine which cells to format":

Choose this option to create a custom formula for your conditional formatting.

Enter the following formula:

1. For example, if your selected month is in cell B3, and the current cell is C6, use the following formula to check if the day is a weekend and if the month matches: =AND(WEEKDAY($B$3&" "&C$5,2)>5,$B$3="December")

This formula checks if the day is Saturday (6) or Sunday (7) and if the month is December. Adjust the month reference as needed.

Set the format:

Click on the "Format" button to set the formatting options, such as shading.

Click "OK" to apply the rule:

Confirm your settings and click "OK" to apply the formatting.

Thank You