Colouring cells based on day of week?

[StargateFan]

Hi.

I have a spreadsheet that has these rows:
B4:G4
B6:G6
B8:G8
B10:G10
B12:G12
B14:G14
B16:G16

A5, A7, A9, A11, A13, A15 and A17 (1 week, 7 days) have the date input
for each row, which can be changed via a macro requesting user input.

Is there a way to get the row cells B:5 above to change colour for the
2 rows that end up corresponding to Saturday and Sunday?

i.e., if user starts the week at Weds. in A5 then Saturday falls on
row B10:G10, and Sunday is B12:G12, etc.

Sorry, it's a bit difficult to describe in words but hopefully I've
made it clear enough. thx

 

[Joel]

You can use conditional formating. Use formula is

the weekday function will return 1 for Sunday and 7 for Saturday

=(or(weekday(A5)=1,weekday(A5)=7))

 

[StargateFan]

You can use conditional formating. Use formula is

the weekday function will return 1 for Sunday and 7 for Saturday

=(or(weekday(A5)=1,weekday(A5)=7))

This is awesome, it works fantastically!

I just ran into one small glitch. I don't know why and I've checked
very carefully, but even when the first row is empty, it shows the
conditional formatting alternate colour. Now once I change the date
it behaves properly and is clear if not a weekend day, but the
instance A5, in this case, is empty, the row gets weekend colouring
back in even though no date is specified.

I know it's most likely tied into the unanticipated fact that A5 is
the only cell in the entire sheet that doesn't contain a formula (?).
Since it's the source cell for the dates in the rest of the sheet, it
truly is blank when there has been no date input into it.

In other words, A5 may be completely blank, but A7 has this:
=IF($A$5<>"",($A$5+1),"")
as well as the equivalent forumulas in A9, A11, A13, A15 and A17.
Anyway, I'm guessing that's why (?).

Would modifying the conditional formatting do the trick, do you think?
I'm not sure if this is the cause, it's just that it seems that
otherwise, it's a heck of a coincidental that the only difference
between cells is this and this is the afflicted row.

Thanks! D

 

[Joel]

A date is just a number with 1 equalling Jan 1, 1900. I assume the blank is
returning a 0 which would be Dec 31,1899. Is this data a weekend???

 

[StargateFan]

A date is just a number with 1 equalling Jan 1, 1900. I assume the blank is
returning a 0 which would be Dec 31,1899. Is this data a weekend???

Not sure I understand the question, since the cell is absolutely blank
when there is no data in it. When user presses a "clear" button, 3
cells get emptied of all data which affects all the dates in the
sheet. A5 is one of the 3 cells. So after that there is nothing in
A5 at all, whereas the other date cells always have formulas similar
to this (I found it was easier working with absolutes, so that is only
change to original sheet's formulas):
=IF($A$5<>"",($A$5+1),"")
though there are no dates in these either, just the formula. So when
no data is in A5, the others clear. And CF carefully checked. I
changed it to absolutes, too, and copied each row and didn't type so
should be no typing errors there on my part.

Don't know what's up with this?? As I said, once a date is dumped
into A5, put there with input from user via macro, then it behaves
according to conditional formatting. Yet when emptied and all rows go
clear, this one remains with CF colour. Yet cells have actually no
colour of their own. Went back and made sure original cell colour is

 

[StargateFan]

Not sure I understand the question, since the cell is absolutely blank
when there is no data in it. When user presses a "clear" button, 3
cells get emptied of all data which affects all the dates in the
sheet. A5 is one of the 3 cells. So after that there is nothing in
A5 at all, whereas the other date cells always have formulas similar
to this (I found it was easier working with absolutes, so that is only
change to original sheet's formulas):
=IF($A$5<>"",($A$5+1),"")

[SNIP]

Fixed! My solution wasn't elegant or anthing and don't ask me why it
works, but it does the job. At some point just now, a flash bulb went
off. Since it was the CF that was affected by a blank cell, or it
seemed to be, then I just changed the formula to not depend on the
contents of the empty cell<duh>!

I changed
=(OR(WEEKDAY($A$5)=1,WEEKDAY($A$5)=7))
to
=(OR(WEEKDAY($A$7-1)=1,WEEKDAY($A$7-1)=7))
and presto. When A5 is blank, row B4:G4 is now clear of all colour.

Go figure ... D