macro for conditional number formatting

  • Thread starter Thread starter scott23
  • Start date Start date
S

scott23

Can anyone help me.

Im trying to write a macro where each time i enter text in column A it
will conditionally format the number in the same row of column C ?

The text that is entered in column A is limited to the list below.



column A(TEXT) column C(format)

ES (####.##)
NQ (####.##)
AB (####.##)
YM (####.##)
ZB (# #/32)
EC (#.####)
JY (###.##)
ED (##.###)
 
Scott,

Worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)

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

ws_exit:
Application.EnableEvents = True
End Sub

Put it in theworksheet code module - right click on the sheet name tab, View
Code

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
How would i modify this so that instead of the entire row
being formatted, it would only be columns E/F/H/I
Thanks
 
See response to other question.

--

HTH

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