How to set conditional format with vba?

[deko]

Can I assign a range to a formula when setting a conditional format?

'compare values in column B to adjacent values in column C
'(lr = last row)
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
'apply formatting
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11

This code results in:
Error Number 5: Invalid procedure call or argument

The problem, I think, is with:
Formula1:="C2:C" & lr

How do I assign conditional formatting to each cell in column B?

Thanks in advance.

 

[Bob Phillips]

It worked okay for me if lr was a valid value, i.e. > 0, although testing a
value to be greater than a range seems odd, and the formula doesn't work
anyway.

Also, best to delete Formatcondition first

Overall, try this

With Workbooks(strXlSFile).Worksheets(sn(i)).Range _
("B2:B" & lr)
..FormatConditions.Delete
..FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, Formula1:="=B2>$C$2:$C$" & lr
..FormatConditions(1).Interior.ColorIndex = 3
End With

although the testr still doesn't make sense to me, but I don't know what you
are trying to do.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[deko]

It worked okay for me if lr was a valid value, i.e. > 0, although testing
a

value to be greater than a range seems odd, and the formula doesn't work
anyway.

Also, best to delete Formatcondition first

Overall, try this

With Workbooks(strXlSFile).Worksheets(sn(i)).Range _
("B2:B" & lr)
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, Formula1:="=B2>$C$2:$C$" & lr
.FormatConditions(1).Interior.ColorIndex = 3
End With

although the testr still doesn't make sense to me, but I don't know what you
are trying to do.

The idea is to compare each value in each row of column B to each value in
each adjacent row in column C.

For example:

If B2 > C2 then font is red. And so on - comparing B3 to C3, B4 to C4, etc.
until the end of the row.

I tried "=B2>$C$2:$C$" & lr - but no luck....

 

[deko]

A similar problem - trying to fill a formula down:

I want column D to show the difference between B and C

D2 = C2-B2
D3 = C3-B3
D4 = C4-B4
and so on

I've figured out how to fill STDEV like this:

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("F2:F" & lr).Formula = "=STDEV(" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 7), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells _
(2, lc)).Address(0, 0) & ")"

But how do I fill a formula that simply subtracts?

Thanks again...

 

[Tom Ogilvy]

Sub HIJ()
Dim sn(0 To 1)
sn(0) = "Sheet1"
strXlSFile = ActiveWorkbook.Name
lr = 20
i = 0

With Workbooks(strXlSFile)
..Activate
..Worksheets(sn(i)).Activate
..Worksheets(sn(i)).Range("B2").Select
With .Worksheets(sn(i)).Range _
("B2:B" & lr)
..FormatConditions.Delete
..FormatConditions.Add _
Type:=xlExpression, _
Operator:=xlGreater, _
Formula1:="=B2>C2"
..FormatConditions(1).Interior.ColorIndex = 3
End With
End With
End Sub

 

[Tom Ogilvy]

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("D2" & lr).Formula = "=C2-B2"


xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("F2:F" & lr).Formula = "=STDEV(B2:" & cells(2,lc).Address(0,0) _
& ")"

But this would cause a circular reference if lc is greater than column F.

 

[deko]

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _

("D2" & lr).Formula = "=C2-B2"

That did the trick. Thanks!

 

[deko]

Sub HIJ()

Dim sn(0 To 1)
sn(0) = "Sheet1"
strXlSFile = ActiveWorkbook.Name
lr = 20
i = 0

With Workbooks(strXlSFile)
.Activate
.Worksheets(sn(i)).Activate
.Worksheets(sn(i)).Range("B2").Select
With .Worksheets(sn(i)).Range _
("B2:B" & lr)
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Operator:=xlGreater, _
Formula1:="=B2>C2"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End With
End Sub

I played around with that but was unsuccessful.

I got this to work, but don't understand why I need to use A1 and B1 instead
of A2 and B2 in the format condition formula. Why does the formula need to
be offset one row?

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Delete

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1<B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1>B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1=B1"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(2).Font.ColorIndex = 9
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(3).Font.ColorIndex = 10

 

[Tom Ogilvy]

It works because you have A1 selected. If you selected Z26 and ran your
macro, you would get much different results. Conditional format formulas
containing relative references, when entered are sensitive to the location
of the activecell.

I gave you code that accounted for this, but you claim you couldn't get it
to work even though it was tested and was doing what you want.

 

[deko]

It works because you have A1 selected. If you selected Z26 and ran your

macro, you would get much different results. Conditional format formulas
containing relative references, when entered are sensitive to the location
of the activecell.

I gave you code that accounted for this, but you claim you couldn't get it
to work even though it was tested and was doing what you want.

I'll try that code again. I may have typoed something.

Thanks for the help.

 

[deko]

you're right - this works like a charm.

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).Select

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Delete

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, _
Formula1:="=B2<C2"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, _
Formula1:="=B2>C2"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Operator:=xlGreater, _
Formula1:="=B2=C2"

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(1).Font.ColorIndex = 11 'blue

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(2).Font.ColorIndex = 9 'red

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
("B2:B" & lr).FormatConditions(3).Font.ColorIndex = 10 'green

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("A1").Select

 

[deko]

you're right - this works like a charm.

correction: on the second time through the loop, I get an error:

Error Number 1004: Select method of Range class failed

The first time is fine, but the second time fails. I tried getting rid of
the Select A1 at the end, but no luck.

 

[deko]

correction: on the second time through the loop, I get an error:

Error Number 1004: Select method of Range class failed

The first time is fine, but the second time fails. I tried getting rid of
the Select A1 at the end, but no luck.

oops....

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Activate

now it works...