If a cell changes, multiply it by another cell.

[snoh8r]

This may be tricky.
I have a spreadsheet with several columns of numbers for sales data. If the cost of an item goes up, I want to be able to enter that cost and have it automatically multiplied by a markup factor contained in another cell. For this instance, my range is C3:L25 and the referencing cell is P3. But, I also want those same cells to change if the markup changes but not the actual sell amount.

I have one macro that when ran does multiply everything. But if I run it again, it multiplies what I already multiplied a second time. So, I made it paste the results as values at the close of each run. But, that doesn't leave me any recourse if the multiplier changes. I tried to write it to trigger on click, but it went into a mad loop. Any help would be appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Const myRange As String = "C3:L25"
 
  If Not Intersect(ActiveCell, Range(myRange)) Is Nothing Then
   myMacro
  End If
End Sub

    Range("Q1").Formula = "=Factor"
    Range("Q1").Copy
    Range("C4:L25").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Columns("C:N").Select
    
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    
    With Range("C4:L25")
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    
    Range("Q1").Formula = ""

The reason for the font change is that I have the Q1 value in white, and realized it copied all to white. I was hoping to somehow keep the Q1 field for updating when I changed entries and so that is why it's white.

 

[GWteB]

You could use the Worksheet_Change event targetting cell P3 and calling myMacro