Need to get values based on multiple conditions through Macro

So far my code is working OK however I dont seem to be getting desired values since all the values are dynamic and change from time to time, hence the need for change, I need to embed this in my code, I have no idea how to acheive it. I need these values only when column D & J is clicked.

Formula:

=IF(H47<=90,G7-(H47*M47)*5%,

IF(H47<=80,G47-(H47*M47)*6%,

IF(H47<=70,G47-(H47*M47)*8%,

IF(H47<=65,G47-(H47*M47)*11%,

IF(H47<=60,G47-(H47*M47)*14%,

IF(H47<=55,G47-(H47*M47)*17%,

IF(H47<=50,G47-(H47*M47)*19%,

IF(H47<=40,G47-(H47*M47)*22%,

IF(H47<=30,G47-(H47*M47)*25%,

))))))))





=IF(A47<=90,G47+(F47*A47)*5%,

IF(A47<=80,G47+(F47*A47)*6%,

IF(A47<=70,G47+(F47*A47)*8%,

IF(A47<=65,G47+(F47*A47)*11%,

IF(A47<=60,G47+(F47*A47)*14%,

IF(A47<=55,G47+(F47*A47)*17%,

IF(A47<=50,G47+(F47*A47)*19%,

IF(A47<=40,G47+(F47*A47)*22%,

IF(A47<=30,G47-(F47*A47)*25%,

))))))))

Current Code in use which doesnt give desired values:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 4 And Target.Column <> 10 Then Range("A3,M3").ClearContents

If Intersect(Target, Range("J4:J76,D4:D76")) Is Nothing Or Target.Count > 1 Then Exit Sub

With Target
    Select Case .Column
        Case [J1].Column
            G = .Offset(, -3).Value
            H = .Offset(, -2).Value
            M = .Offset(, 3).Value
            [M3].Value = WorksheetFunction.MRound(G - (H * M) * 0.1, 0.05)
            
        Case [D1].Column
            G = .Offset(, 3).Value
            A = .Offset(, -3).Value
            F = .Offset(, 2).Value
            [A3].Value = WorksheetFunction.MRound(G + (A * F) * 0.1, 0.05)
                        
    End Select
End With

This formula won't work for two reasons

Formula:

=IF(H47<=90,G7-(H47*M47)*5%,

IF(H47<=80,G47-(H47*M47)*6%,

IF(H47<=70,G47-(H47*M47)*8%,

IF(H47<=65,G47-(H47*M47)*11%,

IF(H47<=60,G47-(H47*M47)*14%,

IF(H47<=55,G47-(H47*M47)*17%,

IF(H47<=50,G47-(H47*M47)*19%,

IF(H47<=40,G47-(H47*M47)*22%,

IF(H47<=30,G47-(H47*M47)*25%,

))))))))

1. The first line of the formula refers to G7, not G47

2. If a value is less than 30, it will also be less than all the higher test values that precede it. So the formula will return the <=90 result. You need to reverse the order of the tests.

The second formula also has the same issue (2).

The point here is to embed the code and this is for the entire column range G4 : G76 , A4 : A76 , F4 to F76, H4 : H76, M4 : M76
the <= sign can be changed to >= depedning on the result I get.
***Corrections***

Formula:

=IF(F49>=90,G49+(F49*A49)*5%,

IF(F49>=80,G49+(F49*A49)*6%,

IF(F49>=70,G49+(F49*A49)*8%,

IF(F49>=65,G49+(F49*A49)*11%,

IF(F49>=60,G49+(F49*A49)*14%,

IF(F49>=55,G49+(F49*A49)*17%,

IF(F49>=50,G49+(F49*A49)*19%,

IF(F49>=40,G49+(F49*A49)*22%,

IF(F49>=30,G49-(F49*A49)*25%,

)))))))))



=IF(H47>=90,G47-(H47*M47)*5%,

IF(H47>=80,G47-(H47*M47)*6%,

IF(H47<=70,G47-(H47*M47)*8%,

IF(H47>=65,G47-(H47*M47)*11%,

IF(H47>=60,G47-(H47*M47)*14%,

IF(H47>=55,G47-(H47*M47)*17%,

IF(H47>=50,G47-(H47*M47)*19%,

IF(H47>=40,G47-(H47*M47)*22%,

IF(H47>=30,G47-(H47*M47)*25%,

)))))))))

I'm not going to spend any time embedding a formula in code when I know it won't work.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.