Conditional Formatting of Numbers

[Bob Phillips]

Debra,

Here is some VBA that will negate the amount if the C value is negative

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column <> 3 Then
.Value = Abs(.Value)
If Me.Cells(.Row, "C").Value < 0 Then
.Value = -.Value
End If
End If
End With

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
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Debra Farnham]

Hello All

WinXP Excel 97

I have a worksheet where if I enter a negative number in Cell C26 (example
only), any other numbers that are entered in row 26 must also be negative.
Unfortunately, I cannot rely upon the users to remember to press the minus
sign.

Thank you in advance for your assistance.

Debra

 

[Debra Farnham]

Thank you TONS Bob!

Works like a charm!

Debra

Debra,

Here is some VBA that will negate the amount if the C value is negative

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column <> 3 Then
.Value = Abs(.Value)
If Me.Cells(.Row, "C").Value < 0 Then
.Value = -.Value
End If
End If
End With

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
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)