Scott,

You're right, 5 is 5 columns beyond the target column.

Here's the code for the other sheet

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 3 Then
If ((.Row = 9 And .Row <= 12) Or _
(.Row 23 And .Row <= 28)) Then
Select Case Target.Value
Case "ES": FormatCells Target, "###0.00"
Case "NQ": FormatCells Target, "###0.00"
Case "ER2": FormatCells Target, "###0.00"
Case "YM": FormatCells Target, "###0.00"
Case "ZB": FormatCells Target, "# ??/32"
Case "EUR": FormatCells Target, "0.0000"
Case "JPY": FormatCells Target, "0.00"
Case "ED": FormatCells Target, "00.000"
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub FormatCells(rng As Range, format As String)
rng.Cells(1, 3).NumberFormat = format
rng.Cells(1, 4).NumberFormat = format
rng.Cells(1, 7).NumberFormat = format
rng.Cells(1, 8).NumberFormat = format
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"scott jeremy" wrote in message
...
Bob,
I think my mistake was that i was assuming '5' was column E and so on...
but i guess it takes the event column and uses that as the start and
thus column E is really 4 since the event column was 2 ? (My
understanding ?)

At any rate, yes now i understand how to get it correct on that sheet.

On the 2nd sheet, yes you are correct i only want the row event to be
triggered by column C rows 9-12 and 23-28.
Thanks again.
scott



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!