Hi,

I am fairly new to excel itself and have been thrown in at the deep end at work. I am writing a rota for colleagues to show when they are not in, in, what work they are doing etc.... To differectiate between different roles I needed more that the 3 conditional formats that excel 2003 allows. I managed to write a crude VBA that does what I want it to do, however, I have 4 work book templates all containing 6 worksheets that need updating with this particular VBA code. In cell ranges B9:AW63 there is a data validation list which contains all the options colleagues can be in whilst at work. At the moment when i reselect the information needed the VBA code kicks in and colours in the cells as instructed, however I was wondering if there was a way due to the amout of sheets that need updating if there was a way for the VBA code to pick up the existing data and automatically change the colour of the cells without me having to go back all over again and pretty much redo all the rotas?

here is the VBA code I have written

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "Not In"
oCell.Interior.ColorIndex = 16
Case Is = "Lunch"
oCell.Interior.ColorIndex = 38
Case Is = "F L"
oCell.Interior.ColorIndex = 4
Case Is = "D F"
oCell.Interior.ColorIndex = 35
Case Is = "B C"
oCell.Interior.ColorIndex = 37
Case Is = "Recs"
oCell.Interior.ColorIndex = 39
End Select
Next oCell
End Sub


Many thanks

griff

Hi Griff,

Am Tue, 19 Jun 2012 12:19:46 +0000 schrieb griffav:

I am fairly new to excel itself and have been thrown in at the deep end
at work. I am writing a rota for colleagues to show when they are not
in, in, what work they are doing etc.... To differectiate between
different roles I needed more that the 3 conditional formats that excel
2003 allows. I managed to write a crude VBA that does what I want it to
do, however, I have 4 work book templates all containing 6 worksheets
that need updating with this particular VBA code. In cell ranges
B9:AW63 there is a data validation list which contains all the options
colleagues can be in whilst at work. At the moment when i reselect the
information needed the VBA code kicks in and colours in the cells as
instructed, however I was wondering if there was a way due to the amout
of sheets that need updating if there was a way for the VBA code to pick
up the existing data and automatically change the colour of the cells
without me having to go back all over again and pretty much redo all the
rotas?

put following code in the module of "ThisWorkbook":

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("B9:AW63")) Is _
Nothing Then Exit Sub

Dim myColor As Integer

Select Case Target.Value
Case Is = "Not In"
myColor = 16
Case Is = "Lunch"
myColor = 38
Case Is = "F L"
myColor = 4
Case Is = "D F"
myColor = 35
Case Is = "B C"
myColor = 37
Case Is = "Recs"
myColor = 39
End Select

Target.Interior.ColorIndex = myColor

End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, June 19, 2012 7:19:46 AM UTC-5, griffav wrote:
Hi,

I am fairly new to excel itself and have been thrown in at the deep end
at work. I am writing a rota for colleagues to show when they are not
in, in, what work they are doing etc.... To differectiate between
different roles I needed more that the 3 conditional formats that excel
2003 allows. I managed to write a crude VBA that does what I want it to
do, however, I have 4 work book templates all containing 6 worksheets
that need updating with this particular VBA code. In cell ranges
B9:AW63 there is a data validation list which contains all the options
colleagues can be in whilst at work. At the moment when i reselect the
information needed the VBA code kicks in and colours in the cells as
instructed, however I was wondering if there was a way due to the amout
of sheets that need updating if there was a way for the VBA code to pick
up the existing data and automatically change the colour of the cells
without me having to go back all over again and pretty much redo all the
rotas?

here is the VBA code I have written

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "Not In"
oCell.Interior.ColorIndex = 16
Case Is = "Lunch"
oCell.Interior.ColorIndex = 38
Case Is = "F L"
oCell.Interior.ColorIndex = 4
Case Is = "D F"
oCell.Interior.ColorIndex = 35
Case Is = "B C"
oCell.Interior.ColorIndex = 37
Case Is = "Recs"
oCell.Interior.ColorIndex = 39
End Select
Next oCell
End Sub


Many thanks

griff




--
griffav

You will want to put in the appropriate event code in the ThisWorkbook module and reference sh. and have it fire when you change the target cell.

Hi thanks for the replies I tried typing in the code suggested by Claus into "workbook" but it didnt work and Don thanks for your reply but I'm so new at this that i dont really understand what you mean would it be possible for you to explain your post to me?

Cheers

Griff

Hi Griff,

Am Sat, 23 Jun 2012 08:59:32 +0000 schrieb griffav:

Hi thanks for the replies I tried typing in the code suggested by Claus
into "workbook" but it didnt work and Don thanks for your reply but I'm
so new at this that i dont really understand what you mean would it be
possible for you to explain your post to me?

copy the code from my first answer. Open your Workbook, press Alt+F11.
Into the project explorer make a doubleclick on ThisWorkbook and paste
the code into the code window.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, June 19, 2012 7:19:46 AM UTC-5, griffav wrote:
Hi,

I am fairly new to excel itself and have been thrown in at the deep end
at work. I am writing a rota for colleagues to show when they are not
in, in, what work they are doing etc.... To differectiate between
different roles I needed more that the 3 conditional formats that excel
2003 allows. I managed to write a crude VBA that does what I want it to
do, however, I have 4 work book templates all containing 6 worksheets
that need updating with this particular VBA code. In cell ranges
B9:AW63 there is a data validation list which contains all the options
colleagues can be in whilst at work. At the moment when i reselect the
information needed the VBA code kicks in and colours in the cells as
instructed, however I was wondering if there was a way due to the amout
of sheets that need updating if there was a way for the VBA code to pick
up the existing data and automatically change the colour of the cells
without me having to go back all over again and pretty much redo all the
rotas?

here is the VBA code I have written

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
Select Case oCell.Value
Case Is = "Not In"
oCell.Interior.ColorIndex = 16
Case Is = "Lunch"
oCell.Interior.ColorIndex = 38
Case Is = "F L"
oCell.Interior.ColorIndex = 4
Case Is = "D F"
oCell.Interior.ColorIndex = 35
Case Is = "B C"
oCell.Interior.ColorIndex = 37
Case Is = "Recs"
oCell.Interior.ColorIndex = 39
End Select
Next oCell
End Sub


Many thanks

griff




--
griffav

Send with complete explanation to dguillett1 @gmail.com

Hi Claus

copy the code from my first answer. Open your Workbook, press Alt+F11.
Into the project explorer make a doubleclick on ThisWorkbook and paste
the code into the code window.

Thanskfor the reply, I have done this and pressed run it is working, however I am having to reselect all the info from my drop down lists for the cells to change colour?

e.g. One cell already has the info "Not In" typed in it. After adding the code, the cell still has no fill but when i re-select "Not In" from the drop down list the cell is coloured in grey.

Is there anything you can suggest for the code to colour in the cells which have already been set up with colleagues work patterns?

Cheers

griffav