Depends upon what you want to do in each worksheet.

If all ranges and cases are equal in each sheet, you can add the code once to
Thisworkbook module and have no code in each sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
code goes here and works on any sheet
End Sub

If ranges and/or cases are different on each sheet, code will go into individual
sheets.

The original code is restricted to "A1". Below is new code to cover a couple of
distinct ranges on a sheet.

BTW, I would recommend using this code instead of the original I posted. I
noticed a glaring flaw in that code and should be dumped.

Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "A1:A10, C1:C34" 'adjust as required

On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target
Select Case .Value
Case Is = "a": .Font.ColorIndex = 3 'red
Case Is = "b": .Font.ColorIndex = 46 'orange
Case Is = "c": .Font.ColorIndex = 10 'green
Case Is = "d": .Font.ColorIndex = 5 'blue
End Select
End With
End If

endit
Application.EnableEvents = True
End Sub


Gord

On Thu, 7 Feb 2008 09:14:08 -0800 (PST), wrote:

Hi,
Also can we make the above code to be generic engouh so that I
need not replicate the same functionality for several work sheets
(sheet1, sheet2, sheet3 etc...) of an excel file. One more thing is
that ....in one sheet say sheet1, if I had two columns in which I need
to apply the color index then...I have replicated your code twice
applying for two different ranges. Can this be optimized?

So in a nutshell....can we have the above piece of code applied
to all the sheets of an excel file cum multiple ranges in a particular
sheet or sheets?

thanks,
Swarna Kumar T.S