Formula Works in cell but not when used in conditional formatting-CodePudding

The formula I am using:

=WEEKDAY(Roster!$K$6:$NL$6, 2) > 5

returns the correct Boolean value however, the conditional formatting is applied to both true and false. I did attempt:

=IF(WEEKDAY(Roster!$K$6:$NL$6, 2) > 5, True, False)

and expected the conditional formatting to only be applied to the cells that returned true but it applies to all of them.

I'm not sure where to go from here.

Conditional formatting Cells effected

The range of cells I am trying to apply it to

CodePudding user response：

I did the same on my PC, entering the date in cells "A2" to "A10". I selected those cells, and used this as a formula:

=WEEKDAY(A2,2)>5

As you see, A2 is the top left cell of the range, and as relative cell referencing is used (no dollarsigns), this makes the formula to be "modified" along with the next cell.

CodePudding user response：

Instead of =WEEKDAY(Roster!$K$6:$NL$6, 2) > 5 I used =WEEKDAY(Roster!$K$6:NL6, 2) > 5 the major difference being I am dynamically applying this formula from left to right so making NL6 No longer a constant and a variable allowed the formula to be used across the document.