Excel Macro to pop-up message based on if and multiplication

[Eraclis]

Hello Everyone!

I ran into an issue with macros and I could use some guidance! I attach a sample excel file for ease of reference.

I need to write a VBA code which needs to:
1) Check if a condition is met ("Other" or "Building") -> Column D

• If "Other", then multiply OMV (Column C) x 0.30%
• If "Building", then multiply OMV (Column C) x 0.75%

2) If the Amount (Column E) is greater than the result of the multiplication, then a warning message should pop-up indicating that we exceeded the permissible amount.

Any ideas would be greatly appreciated.

Thank you in advance!

 

[JackDanIce]

Try:

Sub Macro1()

    Dim v As Variant
    Dim party_legal As Variant
    Dim x As Long
 
    party_legal = Split("Other|0.003|Building|0.0075", "|")
    x = Cells(Rows.Count, 3).End(xlUp).Row
    v = Cells(2, 2).Resize(x - 1, 4).Value
    
    For x = LBound(v, 1) To UBound(v, 1)
        If v(x, 4) > v(x, 2) * CDbl(party_legal(Application.Match(v(x, 3), party_legal, 0))) Then _
            MsgBox "Company " & v(x, 1) & " has exceed permissable amount!", vbExclamation, "Warning"
    Next x
 
    Erase party_legal: Erase v

End Sub

Tested using:

Other.xlsm
	A	B	C	D	E	F	G
1						Calc value	Expected Msgbox
2	1	ABC	100,000	Other	500	300	TRUE
3	2	DEF	200,000	Building	400	1500	FALSE
4	3	GHI	300,000	Other	300	900	FALSE
5	4	JKL	400,000	Building	200	3000	FALSE
6	5	MNO	500,000	Building	100	3750	FALSE
Main

Cell Formulas
Range	Formula
F2:F6	F2	=C2*IF(D2="Other",0.003,0.0075)
G2:G6	G2	=E2>F2

 

[Eraclis]

Thank you very much for the assistance and time spent! To be honest with you, there are a lot of unknown (for me) elements in the macro suggested above. Therefore, I am not sure how the wording should change if the I change the columns' order compared to your tested spreadsheet attached in your reply.

How should I change the macro's write up if columns E, F & G in your tested spreadsheet above are K, L and M in mine?

Thank you in advance!

 

[JackDanIce]

For K, L, M columns, try:

Sub Eraclis()

    Dim v As Variant
    Dim party_legal As Variant
    Dim x As Long
 
    party_legal = Split("Other|0.003|Building|0.0075", "|")
    x = Cells(Rows.Count, 2).End(xlUp).Row
    'Get data from B2:Mx
    v = Cells(2, 2).Resize(x - 1, 12).Value
  
    For x = LBound(v, 1) To UBound(v, 1)
        'If Kx > Cx* factor
        If v(x, 10) > v(x, 2) * CDbl(party_legal(Application.Match(v(x, 3), party_legal, 0))) Then _
            MsgBox "Company " & v(x, 1) & " has exceed permissable amount!", vbExclamation, "Warning"
    Next x
 
    Erase party_legal: Erase v

End Sub