How dio I attach a variable to an Operator:= ?

[Richard Buttrey]

Hi,

I have within a procedure the following code. I set the variables
sRed, SOrange etc. earlier in the procedure, and would also like to
set the Operator:= blah, blah as a variable. I've tried passing it as
a string and variant variable but to no avail.

Can anyone help me out please?

i.e how can I get the bit following the Operator:= to recognise a
variable.?

Usual TIA.

With rMyrange.FormatConditions
.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=sRed
.Add Type:=xlCellValue, Operator:=xlLess,Formula1:=sOrange
.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=sGreen
End With

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Toppers]

Richard,
This works (or you set Opval to 6 = xlLess/ 5 = XlGreater))
opVal = xlLess

With rMyrange.FormatConditions
.Add Type:=xlCellValue, Operator:=opVal, Formula1:=sRed
.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=sOrange
.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=sGreen
End With


HTH

 

[Richard Buttrey]

Hi,

Thanks Toppers,

One supplementary please, I need to set the Type as well. Forgot to
mention this last time :-(

It's either
..Add Type:=xlExpression
or
..Add Type:=xlCellValue

What are the equivalent numbers for these please?
And how do you establish them?

Regards

Richard,
This works (or you set Opval to 6 = xlLess/ 5 = XlGreater))
opVal = xlLess

With rMyrange.FormatConditions
.Add Type:=xlCellValue, Operator:=opVal, Formula1:=sRed
.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=sOrange
.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=sGreen
End With


HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Toppers]

Richard,
XlCellValue=1 and xlExpression=2.

You can determine these by running through the code using F8 key and
highlighting the variable e.g xlCellValue.


opval = 6
Typeval = 1
With rMyrange.FormatConditions
.Add Type:=Typeval, Operator:=opval, Formula1:=sRed


HTH