Simran has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Range.text in and If statement
Hi,
I'm wondering if you can please help with a specific macro I am trying to create.
I'm trying to fill some cells in a dashboard with text and colour, once certain criteria are met.
The colour fill works absolutely fine, but when I added the "and text" string (£, ££ or £££) it doesn't work.
Can you please tell me where I'm going wrong?
Many thanks,
Simran
Sub ApplicationCost()
Dim SumOMS As Long
Dim SumEMS As Long
SumOMS = Application.SumIf(Range("B:G"), "Order Management", Range("G:G"))
SumEMS = Application.SumIf(Range("B:G"), "EMS", Range("G:G"))
If SumOMS > 5000000 Then
Range("K7").Interior.Color = vbRed And Range("K7").Text = "$$$"
ElseIf SumOMS > 2500000 Then
Range("K7").Interior.Color = vbYellow And Range("K7").Text = "$$"
Else:
Range("K7").Interior.Color = vbGreen And Range("K7").Text = "$"
End If
End Sub
RE: Range.text in and If statement
Hi Simran,
Thanks for your question. If you simply want to have £, ££ or £££ in the cell, you could try for example:
Range("K7").Interior.Color = vbRed
Range("K7").Value = "£"
without using "and" in your code
If you want to show the result of the Sumif formatted with £ symbols, you could try:
Range("K7").Interior.Color = vbRed
Range("K7") = format(SumOMS,"£#,##0.00")
which shows the result as a currency. You can use more than one £ symbol here.
I hope this helps.
Kind regards
Marius Barnard
Excel Trainer
RE: Range.text in and If statement
That works perfectly thank you very much!
RE: Range.text in and If statement
Hi Simran,
You're most welcome.
Kind regards
Marius
