Custom formatting in excel to multiply

[maria_b]

Hi,

Is it possible to format cells to automatically multiply a input value by a fixed number and return the product in the same cell? Can this be done in Excel?

Kind regards,

Maria

 

[Joe4]

Things like Custom Formatting and Data Validation can only change the appearance of a cell, not the value.
You can use some Event Procedure VBA code to automatically multiply an entry.

Below is an example that will multiply any numeric entry in cells A1:C10 by 3.
Just right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see in entry is is pre-determined range
    If Not Intersect(Target, Range("A1:C10")) Is Nothing Then
'       Check to see if entry is numeric
        If IsNumeric(Target) Then
            Application.EnableEvents = False
'           Multiply entry by 3
            Target = Target * 3
            Application.EnableEvents = True
        End If
    End If
    
End Sub

Then try making any numeric entry in that range and watch what happens.

 

[maria_b]

Thanks Joe. If I have a number of ranges that I want to set up to automatically multiply a input value, can I do this by adding additional ranges on the third line of the syntax or do I have to run the syntax for each range?

Thanks for your help.

Kind regards,

Maria

 

[Joe4]

You should be able to set up multiple ranges in a single statement like this:

If Not Intersect(Target, Range("A1:C10, M1:P10, X1:Z10")) Is Nothing Then