Conditional formatting or vba ?

[ipbr21054]

Hi,
I have a CF of which the code is =$B1="REFUND" & it triggers the text on that row in question to be RED.

I would like please if REFUND is seen in Column B on the sheet then any value entered in Column D will be changed to Negative value.

Would this be an edit to the working CF at present or a simple separate VBA code ?

Please advise thanks.

 

[Jon von der Heyden]

what should happen if someone removes 'REFUND'?

And must it turn negative, or must the number be negated? I..e. -1 will turn to 1?

And is D formula generated?

 

[ipbr21054]

If REFUND is moved it will be by the row delete option.

If REFUND is shown in column B then in column D of the same row £54.99 is entered it should change itself to -£54.99

Only format for column D is the row D29:D30 so this new code would only apply to the range D5:D28

Thanks

 

[Jon von der Heyden]

Hi

You cannot actually change the value with COnditional Formatting but you can format it to display a negative format (even though it will remain positive).

For VBA, right-click the sheet tab -> View Code and paste this in to the code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("B5:B28")) Is Nothing Then
        On Error Resume Next
            If Target.Value = "REFUND" Then Target.Offset(, 2).Value = -Abs(Target.Offset(, 2).Value)
        On Error GoTo 0
    End If
End Sub

 

[ipbr21054]

Thanks very much for the help.