If cell in column A contains word then format cell in Column B

[Vbanoob98]

Hi I'm trying to figure out how to do what the title says

Currency	Amount
EUR	500.00
USD	600.00
EUR	300.00

So basically if Column A has the word "EUR" then format 500.00 to just 500 and then EUR 300.00 to 300 and so on

Seems simple but what I have found so far seems very convoluted

Thanks a lot for your help

 

[jasonb75]

Just needs conditional formatting with a simple rule and the number format set to Number with 0 decimal places.

Book1
	A	B
1	Currency	Amount
2	EUR	500
3	USD	600.00
4	EUR	300
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B4	Expression	=A2="EUR"	text	NO

 

[Vbanoob98]

Thank you, do you know how to translate this to VBA code?

 

[MARK858]

Thank you, do you know how to translate this to VBA code?

Why do you want to put the conditional formatting into VBA?

 

[Vbanoob98]

Why do you want to put the conditional formatting into VBA?

Because the sheet I need it for gets generated as part of a bigger vba code. I tried with the record macro but no success yet

 

[MARK858]

Sub formatting()

    Dim rng As Range

    Set rng = Range("B2", "B4")
 
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""Eur"""
        .FormatConditions(.FormatConditions.Count).NumberFormat = "0"
    End With

End Sub

 

[Vbanoob98]

Thank you!

And If I wanted to add another constraint how would I do it?

"=$C2<>""50"""


adding that to formula2 is not working

 

[MARK858]

You want it if column A = "Eur" and column C doesn't equal 50?

 

[MARK858]

Just going out so if my last post is true then try...

Sub formattingit()

    Dim rng As Range

    Set rng = Range("B2", "B15")
 
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A2=""Eur"",$C2<>50)"
        .FormatConditions(.FormatConditions.Count).NumberFormat = "0"
    End With

End Sub

Change the range to suit.

 

[Vbanoob98]

Just going out so if my last post is true then try...

Sub formattingit()

    Dim rng As Range

    Set rng = Range("B2", "B15")

    With rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A2=""Eur"",$C2<>50)"
        .FormatConditions(.FormatConditions.Count).NumberFormat = "0"
    End With

End Sub

Change the range to suit.

Yeah that's exactly what I needed. Thank you