Programmatically adding a conditional format

[Maury Markowitz]

I had this working before, but now I can't get the syntax correct...

I am looping over a series of cells in a column and adding a formula
to fetch the latest price from bloomberg. Not every row can be
fetched, perhaps 1/3rd of them on average. There is another column
that holds the last price that the user typed in.

Sometimes these return errors. Sadly they cannot be found using
ISERROR, and you have to LEFT the text instead, looking for the hash.

The user doesn't want to see the error if the fetch doesn't work. So I
have a second column with a formula in it, if the # is there it reads
the user price, if there is no # it reads the bloomberg price.

So far so good.

Now I want to color the border of the cell, to indicate to the user
whether or not the price fetch worked. If it did work, I want it to be
blue, and if it failed (and the original user price is being
displayed) I want it to be red. So here's what I did...

Set rng = Range(userPriceCol & i)
rng.Borders.LineStyle = xlContinuous
rng.Borders.ColorIndex = 5
rng.FormatConditions.Delete
rng.FormatConditions.Add
rng.FormatConditions(1).Formula1 = "=LEFT(" & bbgcol &
i & ",1)=""#"""
rng.FormatConditions(1).Borders.ColorIndex = vbRed

The code fails on "Add", telling me that it has the "wrong number of
arguments..." Then I tried

rng.FormatConditions.Add 'Type:=xlExpression

But this says "argument not optional".

Does anyone know the right syntax here?

Maury

 

[JLGWhiz]

As I read this excerpt from VBA help, you have to specify Type when you use
Add.

Adds a new conditional format. Returns a FormatCondition object that
represents the new conditional format.

expression.Add(Type, Operator, Formula1, Formula2)
expression Required. An expression that returns a FormatConditions object.

Type Required XlFormatConditionType. Specifies whether the conditional
format is based on a cell value or an expression.

XlFormatConditionType can be one of these XlFormatConditionType constants.
xlCellValue The conditional format is based on a cell value.
xlExpression The conditional format is based on an expression.

Operator Optional Variant. The conditional format operator. Can be one of
the following XlFormatConditionOperator constants: xlBetween, xlEqual,
xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual.
If Type is xlExpression, the Operator argument is ignored.

Formula1 Optional Variant. The value or expression associated with the
conditional format. Can be a constant value, a string value, a cell
reference, or a formula.

Formula2 Optional Variant. The value or expression associated with the
second part of the conditional format when Operator is xlBetween or
xlNotBetween (otherwise, this argument is ignored). Can be a constant value,
a string value, a cell reference, or a formula.

 

[JLGWhiz]

Was the apostrophe included when you ran the code with the type specified?
It should works if the constant is spelled correctly and there are no
superflous symbols included.

 

[Peter T]

Following worked for me

Sub test()
Dim bbgcol As String, i As Long
Dim sFml As String
Dim rng As Range

bbgcol = "C"
i = 4

Set rng = Range(bbgcol & i).Offset(, 1)
rng.Offset(, -1) = "#abc"

sFml = "=LEFT($" & bbgcol & "$" & i & ",1)=""#"""
rng.FormatConditions.Delete
rng.FormatConditions.Add Type:=xlExpression, Formula1:=sFml
With rng.FormatConditions(1).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
' or if not sure default red has not been customized
' .color = vbRed ''' Note NOT colorindex
End With

End Sub

If you don't use absolute addressing, as above, the location of the
activecell is fundamental.

Regards,
Peter T

 

[Maury Markowitz]

Working like a champ now. Thanks everyone!

Maury