I'm trying to create a small calendar, which is built like this:

A1: = DATE(2018, 1, 1);

B4: = A1
C4: = A1 + 1
D4: = A1 + 2

This works fine and the columns B2 … D4 looks ok:

Mon, 01/01 | Tue, 02/01 | Wed, 03/01

Now I want to fill all weekends and their following rows gray, like:

Fri, 05/01 (F4, white) | Sat, 06/01 (G4, gray)
white                  | gray
white                  | gray
white                  | gray

I come up with this formula:


which works fine for one cell. Now I wanted to apply this to the whole column, like:

Apply to range: F4:F25
Apply to range: G4:G25

But, this is what it looks like now:

Fri, 05/01 (F4, white) | Sat, 06/01 (G4, gray)
gray                   | gray
gray                   | gray
gray                   | gray

Why isn't this working as expected? Do I need to create a custom format for every single cell? That would be a huge letdown, especially, when copying a cell with conditional formatting, this cell is simply added to the range.


1 Answer 1


you need to lock it down with $ symbol otherwise it will be offset (like in your case) or not working as it should.

the custom formula you are looking for is: =OR(WEEKDAY(B$4)=1, WEEKDAY(B$4)=7) applied to B4:I25

  • Thanks a lot - I just came home and was preparing an example file for your. :D This works perfectly. I didn't know about the $ operator. That's pretty neat. Thank you very much.
    – lampshade
    Commented Sep 4, 2018 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.