Range.text in and If statement

Resolved · Low Priority · Version 2010

Tue 9 Sep 2014, 16:26 Delegate Simran said...

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

Wed 10 Sep 2014, 13:51 Trainer Marius said...

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