Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using colour
i want to compose a sheet with days along the top and timescales down the
side, with different colour keys for different tasks. Is there a way to choose a daily timescale say 0700-1100 and press the task colour key and that timescale cells will be highlighted with the corresponding colour automatically, rather than using the toolbar to highlight the cells? |
#2
|
|||
|
|||
Yes!
But if I understand you right, only through the use of VBA. If, say, you want 5 possible tasks, you could create 5 controls (command buttons - from the control toolbox toolbar) at the top of your sheet. Now if you go to the properties of each of these command buttons, you can change the 'backcolor' of each (I'd suggest you choose a colour from the palette) to what you want - you can also change the caption. Now, I put in some (rather crude) code into VBA that worked. The following happens in this code: 1. Any time you change selection on the sheet, it stores the address of the selected cell into cell L1 (you can use any). 2. When you click on the control, the VBA looks at whatever was last entered in L1 (the last cell selected) and then changes the cell referenced to match the colour of the command button. It can be tidied up (i'm very rusty so forget the prettiest methods - sorry), but it works. Here's the code I put together based on 2 command buttons on the sheet (CommandButton1 and CommandButton2) - if you just create 2 such controls on any sheet (and maybe change the backcolors), then copy and paste the following, you will see how it works and you can play around with it yourself (always the best way to learn!): Private LastCellSelected As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Range("L1").Value = ActiveCell.Address End Sub Private Sub CommandButton1_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton1.BackColor End Sub Private Sub CommandButton2_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton2.BackColor End Sub Hope it makes sense to you, Basil "Bob" wrote: i want to compose a sheet with days along the top and timescales down the side, with different colour keys for different tasks. Is there a way to choose a daily timescale say 0700-1100 and press the task colour key and that timescale cells will be highlighted with the corresponding colour automatically, rather than using the toolbar to highlight the cells? |
#3
|
|||
|
|||
Thank you very much.
It is just what i needed. Again many thanks Bob "Basil" wrote: Yes! But if I understand you right, only through the use of VBA. If, say, you want 5 possible tasks, you could create 5 controls (command buttons - from the control toolbox toolbar) at the top of your sheet. Now if you go to the properties of each of these command buttons, you can change the 'backcolor' of each (I'd suggest you choose a colour from the palette) to what you want - you can also change the caption. Now, I put in some (rather crude) code into VBA that worked. The following happens in this code: 1. Any time you change selection on the sheet, it stores the address of the selected cell into cell L1 (you can use any). 2. When you click on the control, the VBA looks at whatever was last entered in L1 (the last cell selected) and then changes the cell referenced to match the colour of the command button. It can be tidied up (i'm very rusty so forget the prettiest methods - sorry), but it works. Here's the code I put together based on 2 command buttons on the sheet (CommandButton1 and CommandButton2) - if you just create 2 such controls on any sheet (and maybe change the backcolors), then copy and paste the following, you will see how it works and you can play around with it yourself (always the best way to learn!): Private LastCellSelected As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Range("L1").Value = ActiveCell.Address End Sub Private Sub CommandButton1_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton1.BackColor End Sub Private Sub CommandButton2_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton2.BackColor End Sub Hope it makes sense to you, Basil "Bob" wrote: i want to compose a sheet with days along the top and timescales down the side, with different colour keys for different tasks. Is there a way to choose a daily timescale say 0700-1100 and press the task colour key and that timescale cells will be highlighted with the corresponding colour automatically, rather than using the toolbar to highlight the cells? |
#4
|
|||
|
|||
You're welcome. Thanks for the feedback!
Baz "Bob" wrote: Thank you very much. It is just what i needed. Again many thanks Bob "Basil" wrote: Yes! But if I understand you right, only through the use of VBA. If, say, you want 5 possible tasks, you could create 5 controls (command buttons - from the control toolbox toolbar) at the top of your sheet. Now if you go to the properties of each of these command buttons, you can change the 'backcolor' of each (I'd suggest you choose a colour from the palette) to what you want - you can also change the caption. Now, I put in some (rather crude) code into VBA that worked. The following happens in this code: 1. Any time you change selection on the sheet, it stores the address of the selected cell into cell L1 (you can use any). 2. When you click on the control, the VBA looks at whatever was last entered in L1 (the last cell selected) and then changes the cell referenced to match the colour of the command button. It can be tidied up (i'm very rusty so forget the prettiest methods - sorry), but it works. Here's the code I put together based on 2 command buttons on the sheet (CommandButton1 and CommandButton2) - if you just create 2 such controls on any sheet (and maybe change the backcolors), then copy and paste the following, you will see how it works and you can play around with it yourself (always the best way to learn!): Private LastCellSelected As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Range("L1").Value = ActiveCell.Address End Sub Private Sub CommandButton1_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton1.BackColor End Sub Private Sub CommandButton2_Click() LastCellSelected = ActiveSheet.Range("L1").Value ActiveSheet.Range(LastCellSelected).Interior.Color = CommandButton2.BackColor End Sub Hope it makes sense to you, Basil "Bob" wrote: i want to compose a sheet with days along the top and timescales down the side, with different colour keys for different tasks. Is there a way to choose a daily timescale say 0700-1100 and press the task colour key and that timescale cells will be highlighted with the corresponding colour automatically, rather than using the toolbar to highlight the cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to change the blue colour of an activated Auto Filter button? | Excel Discussion (Misc queries) | |||
Problems with Colour Printing | Excel Discussion (Misc queries) | |||
Macro button colour change??? | Excel Worksheet Functions | |||
Text in Blue colour, but print in black colour | Excel Worksheet Functions | |||
Function to return colour of formatted cell | Excel Worksheet Functions |