Applying a single conditional formatting rule across a range

Question

I'm building a yearly shift roster and I'm trying to figure out how to apply the conditional formatting to show where the weekends are. Figuring out how to define the weekend is easy, the problem here is getting the conditional formatting to cooperate with that rule.

Here's how I have my table set up for anyone trying to replicate this:

Row 1 - =LEFT(TEXT(row 2,"aaa"),1) this displays the single letter day of the week based on the date in row 2
Row 2 - The date, formatted to DD. A2 starts with 1/1/2016, B2 is =A2+1 and that is repeated all the way to column NB (=NA+1)

The conditional formatting formula I'm using is =WEEKDAY($A$2,2)>5, applied to $A$1:$A$15. Now this works great for column A, but when trying to copy it over to column B the rule is still referencing column A (however it will apply it to column B). What I'm struggling to figure out is how to get the conditional formatting rule to look at all 365 columns and then apply the conditional formatting to that single column and not the entire range. The end result here would be that the weekdays have no fill applied while the weekends are shaded.

Try changing the =TEXT(row2,"aaa") to =TEXT(row2,"DDD"). Then change the co distil all formatting to =IF(A2="S",TRUE,FALSE) make sure not to have your references locked down (is don't use $) — Henry W
Ok that almost fixed it. I set the applicable range to be A1:NB2, but it's only highlighting the first row, and not highlighting the column under it — Chaosbydesign
Use the format painter to select all that row and the. Apply it to the rows below and it should work, assuming you've not used any absolute references ($) — Henry W

Chaosbydesign Chaosbydesign · Accepted Answer · 2016-02-25T20:35:09

The solution to this is to use =WEEKDAY(INDIRECT(ADDRESS(2,COLUMN())),2)>5.

Here's what's going on in each part, starting inside and working out:
COLUMN() returns the column number of the current cell (A=1, B=2, C=3, etc...), so for the sake of this example let's say you had cell A2 selected, COLUMN() will then return 1.
ADDRESS(2,1) returns a TEXT string of the absolute cell reference ("$A$2"), in this case locked to row 2.
INDIRECT("$A$2") converts the text string into the A2 cell reference.
WEEKDAY($A$2,2) evaluates the date in cell A2 (1/1/2016) and returns a numerical value based on what date of the week it is. The 2 argument sets the week as Monday (1) through Sunday (7). In this example WEEKDAY() will return a value of 5 (Friday).

Applying a single conditional formatting rule across a range

1 Answers