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