Conditional Cell Number Formatting

[Worf]

I've looked back on several thousand messages or so and haven't seen
this so here goes. I need to be able to set the numerical format of a
cell based upon the contents of another cell. In other words if C1 is
equal to x than the numerical format of C2 would be "00000" and if C1
equals y then the C2 cell's format would need to be set as
000000000000. I hope I've been clear, thanks!! All i could find in the
usual help channels was concerning the formatting of contents with
respect to font, colors, etc.

 

[Bob Phillips]

VBA?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "x" Then
.Offset(0, 1).NumberFormat = "00000"
ElseIf .Value = "y" Then
.Offset(0, 1).NumberFormat = "000000000000"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Worf]

VBA?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "x" Then
.Offset(0, 1).NumberFormat = "00000"
ElseIf .Value = "y" Then
.Offset(0, 1).NumberFormat = "000000000000"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Bob, thanks so much!! I'll try it now.

 

[Worf]

Bob, thanks so much!! I'll try it now.

Hi again Bob, one more question if I may, how would I apply this to a
group of cells ie. c1 to c50? Thanks again!!!

 

[Bob Phillips]

Worf,

Change the line

Const WS_RANGE As String = "C1"

to

Const WS_RANGE As String = "C1:C50"

This will monitor each cell, and set the adjacent cell format as each
changes, it won't do it all at once.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Worf]

Worf,

Change the line

Const WS_RANGE As String = "C1"

to

Const WS_RANGE As String = "C1:C50"

This will monitor each cell, and set the adjacent cell format as each
changes, it won't do it all at once.

Hi again Bob, not sure if I'm doing this right.. This coding is pasted
into the dialog box that says "Book1 - Sheet1 (Code)"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "x" Then
.Offset(0, 1).NumberFormat = "00000"
ElseIf .Value = "y" Then
.Offset(0, 1).NumberFormat = "000000000000"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

The code window now has two sections : Declarations and Change

I then save the worksheet and reopen, the enable macros dialog comes
up and I enable it. But I'm not seeing any changes being made to the
formatting of numbers placed into B colum. Thanks so much, Kevin

 

[Worf]

Hi again Bob, not sure if I'm doing this right.. This coding is pasted
into the dialog box that says "Book1 - Sheet1 (Code)"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "x" Then
.Offset(0, 1).NumberFormat = "00000"
ElseIf .Value = "y" Then
.Offset(0, 1).NumberFormat = "000000000000"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

The code window now has two sections : Declarations and Change

I then save the worksheet and reopen, the enable macros dialog comes
up and I enable it. But I'm not seeing any changes being made to the
formatting of numbers placed into B colum. Thanks so much, Kevin

Bob, I got it!!! I was dropping the number in colum b when the code
was looking for a number in colum d this now works perfectly!!!
Again, thank you so much, Kevin

 

[Bob Phillips]

You beat me to the reply <G>.

Glad it's sorted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)