help worksheet event code
 

Hi,
I received help writing this worksheet event code, but i was hoping to
change 1 small thing.

1. Id like to format only the cells in columns E,F,H,I as opposed to
the whole row.

Here is the code i currently have that formats the whole row rather
than what i need.
================================================== =============================

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case Target.Value
Case "ES": Target.EntireRow.NumberFormat = "(###0.00)"
Case "NQ": Target.EntireRow.NumberFormat = "(###0.00)"
Case "AB": Target.EntireRow.NumberFormat = "(###0.00)"
Case "YM": Target.EntireRow.NumberFormat = "(###0.00)"
Case "ZB": Target.EntireRow.NumberFormat = "(# ??/32)"
Case "EC": Target.EntireRow.NumberFormat = " (#.0000)"
Case "JY": Target.EntireRow.NumberFormat = " (##0.00)"
Case "ED": Target.EntireRow.NumberFormat = " (#0.000)"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

help worksheet event code
 

Scott,

It's best to keep to the original thread, helps us know where we are.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case Target.Value
Case "ES": FormatCells Target, "(###0.00)"
Case "NQ": FormatCells Target, "(###0.00)"
Case "AB": FormatCells Target, "(###0.00)"
Case "YM": FormatCells Target, "(###0.00)"
Case "ZB": FormatCells Target, "(# ??/32)"
Case "EC": FormatCells Target, " (#.0000)"
Case "JY": FormatCells Target, " (##0.00)"
Case "ED": FormatCells Target, " (#0.000)"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub FormatCells(rng As Range, format As String)

rng.Cells(1, 4).NumberFormat = format
rng.Cells(1, 5).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)

"scott23" wrote in message
om...
Hi,
I received help writing this worksheet event code, but i was hoping to
change 1 small thing.

1. Id like to format only the cells in columns E,F,H,I as opposed to
the whole row.

Here is the code i currently have that formats the whole row rather
than what i need.

================================================== ==========================
===

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case Target.Value
Case "ES": Target.EntireRow.NumberFormat = "(###0.00)"
Case "NQ": Target.EntireRow.NumberFormat = "(###0.00)"
Case "AB": Target.EntireRow.NumberFormat = "(###0.00)"
Case "YM": Target.EntireRow.NumberFormat = "(###0.00)"
Case "ZB": Target.EntireRow.NumberFormat = "(# ??/32)"
Case "EC": Target.EntireRow.NumberFormat = " (#.0000)"
Case "JY": Target.EntireRow.NumberFormat = " (##0.00)"
Case "ED": Target.EntireRow.NumberFormat = " (#0.000)"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

help worksheet event code
 

try this. Modify col G as desired

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 2 Then Exit Sub
x = ActiveCell.Row
Set myrng = Range(Cells(x, "e"), Cells(x, "i"))
Select Case UCase(Target)
Case "ES", "NQ", "AB", "YM": myrng.NumberFormat = "###0.00"
Case "ZB": myrng.NumberFormat = "# ??/32"
Case "EC", "ED": myrng.NumberFormat = "0.0000"
Case "JY": myrng.NumberFormat = "##0.00"
End Select
cells(x,"G").numberformat="0.000000000000000"
End Sub
--
Don Guillett
SalesAid Software

"scott23" wrote in message
om...
Hi,
I received help writing this worksheet event code, but i was hoping to
change 1 small thing.

1. Id like to format only the cells in columns E,F,H,I as opposed to
the whole row.

Here is the code i currently have that formats the whole row rather
than what i need.

================================================== ==========================
===

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Select Case Target.Value
Case "ES": Target.EntireRow.NumberFormat = "(###0.00)"
Case "NQ": Target.EntireRow.NumberFormat = "(###0.00)"
Case "AB": Target.EntireRow.NumberFormat = "(###0.00)"
Case "YM": Target.EntireRow.NumberFormat = "(###0.00)"
Case "ZB": Target.EntireRow.NumberFormat = "(# ??/32)"
Case "EC": Target.EntireRow.NumberFormat = " (#.0000)"
Case "JY": Target.EntireRow.NumberFormat = " (##0.00)"
Case "ED": Target.EntireRow.NumberFormat = " (#0.000)"
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub