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?

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?

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?

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?