HELP!!! I have written my 1st ever VBA code and need more advice....

[griffav]

Hi,

I am fairly new to excel itself and have been thrown in at the deep en
at work. I am writing a rota for colleagues to show when they are no
in, in, what work they are doing etc.... To differectiate betwee
different roles I needed more that the 3 conditional formats that exce
2003 allows. I managed to write a crude VBA that does what I want it t
do, however, I have 4 work book templates all containing 6 worksheet
that need updating with this particular VBA code. In cell range
B9:AW63 there is a data validation list which contains all the option
colleagues can be in whilst at work. At the moment when i reselect th
information needed the VBA code kicks in and colours in the cells a
instructed, however I was wondering if there was a way due to the amou
of sheets that need updating if there was a way for the VBA code to pic
up the existing data and automatically change the colour of the cell
without me having to go back all over again and pretty much redo all th
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

grif

 

[Claus Busch]

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

 

[Don Guillett]

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

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.

 

[griffav]

;1602907']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 dee end
at work. I am writing a rota for colleagues to show when they ar not
in, in, what work they are doing etc.... To differectiate between
different roles I needed more that the 3 conditional formats tha excel
2003 allows. I managed to write a crude VBA that does what I want i 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 th options
colleagues can be in whilst at work. At the moment when i reselec 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 th amout
of sheets that need updating if there was a way for the VBA code t 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 al 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

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

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

Cheers

Grif

 

[Claus Busch]

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

 

[Don Guillett]

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

Send with complete explanation to dguillett1 @gmail.com

 

[griffav]

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 fo
the cells to change colour?

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

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

Cheers

griffa