Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
On Sat, 8 Mar 2008 19:20:01 -0800, Joel
wrote: 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! :oD "StargateFan" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
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" wrote: On Sat, 8 Mar 2008 19:20:01 -0800, Joel wrote: 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! :oD "StargateFan" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
On Sun, 9 Mar 2008 07:53:02 -0700, Joel
wrote: 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 clear. It's very odd. <g "StargateFan" wrote: On Sat, 8 Mar 2008 19:20:01 -0800, Joel wrote: 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! :oD "StargateFan" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colouring cells based on day of week?
On Sun, 09 Mar 2008 10:13:38 -0500, StargateFan
wrote: On Sun, 9 Mar 2008 07:53:02 -0700, Joel wrote: 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),"") [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 ... :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically copy cells from two week day sheets based from today | Excel Discussion (Misc queries) | |||
Automatic colouring of cells | Excel Discussion (Misc queries) | |||
Automatic colouring of cells | Excel Worksheet Functions | |||
colouring cells that are between two dates | Excel Programming | |||
Colouring cells | Excel Programming |