| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing the colour of a range of cells, some with formulas.
Hi there
I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
Conditional formatting, using the WEEKDAY function.
Info on both is available in Excel help. -- David Biddulph "Champ" wrote in message ... Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
You would use conditional formatting to do this - available from the
format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31*am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
Thanks Pete, this almost works but by referencing the whole sheet with this
condition turns all cells grey, except for the actual dates that are not weekend. I don't understand what the ",2" & "5" means. The conditional format I'm using is: =WEEKDAY($A$3:$L$40,2)5, and the cells with the dates in them are in row 3. Thanks in advance. Champ "Pete_UK" wrote: You would use conditional formatting to do this - available from the format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31 am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
If you don't understand the WEEKDAY function, type the word WEEKDAY into
Excel help. Secondly, you need only one cell reference in the CF formula condition, not a range. You will also need to understand the difference between absolute and relative addressing (which again can be found in Excel help). If you are trying to format the range A3:L40 based on the content of row 3 in each column, highlight the range, and if you've got A3 as the active cell, use the CF/ Formula Is condition =WEEKDAY(A$3,2)5 If you look in other columns and other rows, you will see that each cell's CF condition will now be referencing the row 3 cell for the relevant column. -- David Biddulph "Champ" wrote in message ... Thanks Pete, this almost works but by referencing the whole sheet with this condition turns all cells grey, except for the actual dates that are not weekend. I don't understand what the ",2" & "5" means. The conditional format I'm using is: =WEEKDAY($A$3:$L$40,2)5, and the cells with the dates in them are in row 3. Thanks in advance. Champ "Pete_UK" wrote: You would use conditional formatting to do this - available from the format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31 am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
I presume you mean that your dates are in column 3, rather than row 3.
Highlight the range A3:L40, starting from A3 (so that it is the active cell), then click on Format | Conditional formatting. Choose Formula Is, and then enter this formula: =WEEKDAY($C3,2)5 Click on the Format button and then choose grey in the Patterns tab, then click OK twice to exit. Excel will automatically adjust that cell reference to suit each row in the highlighted area, and each row will refer to column C (hence the $C3). This will turn the row from A to L grey if the date in C is a weekend. WEEKDAY(cell,2) returns numbers 1 to 7, where 1 = Monday, 2 = Tuesday, 6 = Satuday, 7 = Sunday, etc., so if you are looking for weekends you want values greater than 5. Hope this helps. Pete On Feb 2, 12:26*pm, Champ wrote: Thanks Pete, this almost works but by referencing the whole sheet with this condition turns all cells grey, except for the actual dates that are not weekend. I don't understand what the ",2" & "5" means. The conditional format I'm using is: =WEEKDAY($A$3:$L$40,2)5, and the cells with the dates in them are in row 3. Thanks in advance. Champ "Pete_UK" wrote: You would use conditional formatting to do this - available from the format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31 am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i..e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
You've been a great help. Excel help is useful when you know what you're
looking for. Cheers. "David Biddulph" wrote: If you don't understand the WEEKDAY function, type the word WEEKDAY into Excel help. Secondly, you need only one cell reference in the CF formula condition, not a range. You will also need to understand the difference between absolute and relative addressing (which again can be found in Excel help). If you are trying to format the range A3:L40 based on the content of row 3 in each column, highlight the range, and if you've got A3 as the active cell, use the CF/ Formula Is condition =WEEKDAY(A$3,2)5 If you look in other columns and other rows, you will see that each cell's CF condition will now be referencing the row 3 cell for the relevant column. -- David Biddulph "Champ" wrote in message ... Thanks Pete, this almost works but by referencing the whole sheet with this condition turns all cells grey, except for the actual dates that are not weekend. I don't understand what the ",2" & "5" means. The conditional format I'm using is: =WEEKDAY($A$3:$L$40,2)5, and the cells with the dates in them are in row 3. Thanks in advance. Champ "Pete_UK" wrote: You would use conditional formatting to do this - available from the format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31 am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i.e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
Thank you Pete, and I did mean row 3, not column 3. It's all good though,
cheers. "Pete_UK" wrote: I presume you mean that your dates are in column 3, rather than row 3. Highlight the range A3:L40, starting from A3 (so that it is the active cell), then click on Format | Conditional formatting. Choose Formula Is, and then enter this formula: =WEEKDAY($C3,2)5 Click on the Format button and then choose grey in the Patterns tab, then click OK twice to exit. Excel will automatically adjust that cell reference to suit each row in the highlighted area, and each row will refer to column C (hence the $C3). This will turn the row from A to L grey if the date in C is a weekend. WEEKDAY(cell,2) returns numbers 1 to 7, where 1 = Monday, 2 = Tuesday, 6 = Satuday, 7 = Sunday, etc., so if you are looking for weekends you want values greater than 5. Hope this helps. Pete On Feb 2, 12:26 pm, Champ wrote: Thanks Pete, this almost works but by referencing the whole sheet with this condition turns all cells grey, except for the actual dates that are not weekend. I don't understand what the ",2" & "5" means. The conditional format I'm using is: =WEEKDAY($A$3:$L$40,2)5, and the cells with the dates in them are in row 3. Thanks in advance. Champ "Pete_UK" wrote: You would use conditional formatting to do this - available from the format menu. You need to choose Formula Is rather than Cell Value Is in the first box of the pop-up, and then enter this formula: =WEEKDAY(cell_ref,2)5 then click on the Format button, select the Patterns tab (for background colour) and choose grey. Then click OK twice to exit the dialogue boxes. Hope this helps. Pete On Feb 2, 9:31 am, Champ wrote: Hi there I'm hoping someone can assist me. I am wanting a range of cells shaded grey, one of which includes a formula that specifies its date, and if the date falls on a Saturday and Sunday they're to be shaded grey. Some more information that may help. The workbook contains 4 sheets, one for each quarter, and for each quarter I want to just have to enter one date i..e 01/03/2009 formatted to 1-Mar-09, the rest of the date specific columns across the sheet are populated by & simple '=c3+1' formula but then if that date is a Saturday and Sunday, I'd like it and several rows below it, shaded grey. Hope this makes sense. Cheers, Champ- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Changing the colour of a range of cells, some with formulas.
You're welcome - thanks for feeding back.
Pete On Feb 3, 5:41*am, Champ wrote: Thank you Pete, and I did mean row 3, not column 3. It's all good though, cheers. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing all cells in one colour to a different colour | Excel Discussion (Misc queries) | |||
| Changing fill colour of cells in a range | Excel Discussion (Misc queries) | |||
| Changing font colour in some cells | New Users to Excel | |||
| changing the colour of cells depending on the content. | Excel Discussion (Misc queries) | |||
| Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) | |||
Linear Mode
