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

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
__________________________

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" wrote:

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

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


On Thu, 27 Oct 2005 09:06:02 -0700, "Toppers"
<Toppers@discussions.microsoft.com> wrote:

>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" wrote:
>
>> 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
>> __________________________
>>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

"Richard Buttrey" wrote:

> 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
>
>
> On Thu, 27 Oct 2005 09:06:02 -0700, "Toppers"
> <Toppers@discussions.microsoft.com> wrote:
>
> >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" wrote:
> >
> >> 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
> >> __________________________
> >>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>