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