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
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