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)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
'apply formatting
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("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.

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" wrote in message
m...
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)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlGreater, Formula1:="C2:C" & lr
'apply formatting
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("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.

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....

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)).Rang e _
("F2:F" & lr).Formula = "=STDEV(" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 7), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s _
(2, lc)).Address(0, 0) & ")"

But how do I fill a formula that simply subtracts?

Thanks again...

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:="=B2C2"
..FormatConditions(1).Interior.ColorIndex = 3
End With
End With
End Sub

--
Regards,
Tom Ogilvy

"deko" wrote in message
...
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....

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("D2:D" & lr).Formula = "=C2-B2"


xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("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.

--
Regards,
Tom Ogilvy



"deko" wrote in message
m...
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)).Rang e _
("F2:F" & lr).Formula = "=STDEV(" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 7), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s _
(2, lc)).Address(0, 0) & ")"

But how do I fill a formula that simply subtracts?

Thanks again...

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("D2:D" & lr).Formula = "=C2-B2"

That did the trick. Thanks!

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:="=B2C2"
.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)).Rang e _
("B2:B" & lr).FormatConditions.Delete

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

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1B1"

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

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

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.



--
Regards,
Tom Ogilvy

"deko" wrote in message
.. .
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:="=B2C2"
.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)).Rang e _
("B2:B" & lr).FormatConditions.Delete

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

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
("B2:B" & lr).FormatConditions.Add _
Type:=xlExpression, Formula1:="=A1B1"

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

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

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.

you're right - this works like a charm.

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

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

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

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

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

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

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

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

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

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.

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)).Acti vate

now it works...