CODEWITHSUNDEEP
excelcalendarconditional-formatting
rohan verma
rohan verma

Reputation: 11

Excel - Shading entire Column based on one value

i am trying to make a work calendar for myself. screenshot of the excel file

I have written a conditional formatting to color all saturdays and sundays with the following code

=OR(WEEKDAY(B2)=2,WEEKDAY(B2)=1)

how can i make the Entire column under that day be colored.

Thanks :)

Upvotes: 1

Views: 70

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34390

In Conditional formatting | Use a formula...

=WEEKDAY(B$2,16)<3

extended from B2 down and across.

Upvotes: 1

Daniel M
Daniel M

Reputation: 1

Looks like you need to add a relative cell reference on B2:

=OR(WEEKDAY(B$2)=2,WEEKDAY(B$2)=1)

Here's an article about using relative references in conditional formatting: https://www.ablebits.com/office-addins-blog/2014/08/07/relative-absolute-cell-references-excel-conditional-formatting/

Upvotes: 0

pnuts
pnuts

Reputation: 59495

If you are not going to be moving columns around (Friday will always be in whichever cell it is at present and days just continue in sequence to the right) it might be easier to select the entire range to be subject to formatting and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=OR(MOD(COLUMN(),7)=3,MOD(COLUMN(),7)=4)

Format..., select your choice of Fill, OK, OK.

Adjust 3 and 4 to match whichever day you choose to start the sequence with wherever your selected range starts.

Given information you have withheld of what is where the above might be simplified.

Upvotes: 1

Related Questions