auto highlight columns based on day of the week

[lakke2120]

Hello,
I have a problem that I have not been able to find any help on. I have spreadsheets that has 31 columns to account for each day of the every month. Each column has about 17 rows in them. I would like to be able to enter a specific day or range of days such as monday thru Friday, or Wednesday and Saturday, or even one day, etc. and have them highlighted a certain shade of gray so that my students know which days they have to do a particular task. I have to do this several dozen times a day, for months in advance, so I end up having to look up each month to identify which days I need to highlight and then manually do it. It would be great to have a tool or box on the side of the each spreadsheet where I can enter the day(s) of the week I need highlighted for any particular month and have the columns highlighted automatically. I am a teacher and it takes hours each week just to do this. I figured its a difficult thing to do which is why non of my colleagues have been able to find help on this. Any help would be greatly appreciated. Thank you

 

[RickXL]

Hi,

It sounds "doable". I think the spec might need some work, though.

You have many worksheets with one month per worksheet.
When you enter, say, "MO" do you want every Monday on every worksheet to turn grey? Or is there an implied date range somewhere?

 

[RonB1111]

You can do this with Conditional Formatting.
Since you didn't provide sample data, I'm going to assume you have a worksheet with dates (eg: Jan 1, 2015, Jan 2, 2015, etc) entered in B2:AF2 and data is enter below them thru, say, row 30.

First, on a blank sheet (tab) within that workbook, change the tab name to "days" (without the quotes).
Now on your worksheet with the dates in B2:AF2,
1. Select B2:AF30
2. Click Home - Conditional Formatting -New Rule
3. Click "Use a formula..."
4. Copy the below formula into the box below the phrase "Format Values where this formula is true:"

=OR(WEEKDAY(B$2,11)=days!$A$1,WEEKDAY(B$2,11)=days!$A$2,WEEKDAY(B$2,11)=days!$A$3,WEEKDAY(B$2,11)=days!$A$4,WEEKDAY(B$2,11)=days!$A$5,WEEKDAY(B$2,11)=days!$A$6,WEEKDAY(B$2,11)=days!$A$7)

5. Click "Format", then the Fill tab, then select a color, then click OK, OK

Now on the "days" tab use cells A1 thru A7 to enter the days you want color highlighted. Specifically, enter the numbers 1 thru 7 to represent Mon thru Sun respectively. Enter only 1 number in each cell. If you want both Mon and Wed highlighted, then enter 1 in A1 and 3 in A2. Actually you can enter in any cell A1:A7 and in any sort order. Now go to your other worksheet tab any you'll see that Mon and Wed dates are highlighted. To return a column to an unhighlighted status, go back to the "days" worksheet and delete the appropriate number in A1:A7.

You can repeat Conditional Formatting steps 1-5 above on all other worksheets in this workbook and they will all highlight whatever columns represent the days you entered on the "days" worksheet.

If your data is set up differently and you need help adjusting the formula/setup, please describe your set up, or upload the file (but 1st remove any confidential data) to a file sharing service and post the link to open it here.

 

[lakke2120]

thank you very much

 

[RonB1111]

You're welcome! - Is it working as you expected?

 

[lakke2120]

thank you very much. Each spreadsheet is in a different workbook and I believe I will have to do each spreadsheet one at a time because every student has different days that need to be highlighted. I am hoping to be able to enter days of the week in terms like "m-w-f" somewhere on my spreadsheet and have the corresponding columns (which are identified by numbers 1 thru 31) highlighted. The month is located in cell A1 and I change that according to whatever month I need. So, for example, if I were to enter "m-w-f" in a cell and A1 is July 2015, the columns 6,13,20,27(Mondays) and 1,8,15,22,29 (Wednesdays) and 3,10,17,24,31 (Fridays) get highlighted from C17:AG34. I hope I explained the issue effectively. Thank you again for your quick response.

 

[lakke2120]

thank you I will try that and let you know

 

[RonB1111]

If you enter a 1st of the month date into A1, (eg: 7/1/2015) so that it is in Excel "date format", it can simplify several things:

1. You could then do a custom format for A1 of "mmmm yyyy" (without quotes) so it will display the full month name as July 2015. If you want it displayed abbreviated, use the custom format "mmm yyy". To enter a custom format, click Home-FormatCells then click the Number tab, then Custom, then enter the custom format in the box immediately below the word "Type", then OK.

2. Assuming your numbers 1-31 are in B2:AF2, You could enter these formulas:

in B2:  =A1
in C2:  =IF(MONTH(B2+1)<>MONTH($A$1),"",B2+1)
Then copy C2 across to AF2

Then do a custom format of "d" on B2:AF2 as described above and the cells will only display the day numbers 1-31. The advantage of this is it makes the formulas for Conditional Formatting easier and you can use the formula I previously gave but still have your worksheet display them the way it currently does.

You don't have to use a separate "days" tab to select the days to highlight. You could use any 7 contiguous cells on your regular worksheet if you prefer - I'd just need to know which cells you'd want to use. Also, you could use adjacent cells to the 7 I'm currently using for reminders (eg: Monday-1 Tuesday-2 etc) so you know which numbers to enter. If you preferred entering a letter rather than a number I could create a formula that converts that to the appropriate number - I'll need to know what letter(s) you'd use for each day of the week. If you preferred entering them all in a single cell, I could also create formulas to accommodate that - I'll need to know the exact format you'd enter it. Please explain which approach you'd like and I'll modify the formula's accordingly.