CODEWITHSUNDEEP
excelvbams-office
user3495234
user3495234

Reputation: 147

How to update macro when cell changes in VBA

So I'm new to VBA and I wanted to convert a bunch of employee hours to dollars by multiplying them by the hourly rate and vice versa. However I'm not sure how to handle a change in the hourly rate. So currently my code looks like this:

Sub dollarHour()

    Dim hrs As Range

    Set hrs = Range("D3:N33")

    For Each cell In hrs
        If cell.NumberFormat = "0.00" Then
            cell.Value = cell * Range("P37")
            cell.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

        Else
            cell.Value = cell / Range("P37")
            cell.NumberFormat = "0.00"

        End If

    Next

End Sub

So basically my issue is the "P37" (the dollar multiplier) could be changed but it won't affect the cells until I run the macro again which changes up the original employee hours (which is something I don't want). So if I changed "P37" from $25 to $26 dollars for example, I want the cells in hrs to reflect that change. Any help would be appreciated.

Upvotes: 0

Views: 50

Answers (1)

Darrell H
Darrell H

Reputation: 1886

Use a Worksheet_Change event. This will run your macro every time $P$37 changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$P$37" Then
        dollarhour
    End If
End Sub

Upvotes: 1

Related Questions