Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I want to
do a formula in the CF formula box. The user can change the value in K1 and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a concatenation.
It seems that CF can only accept a real formula and not a one built as the
result of an expression.

Any idea? Thank you.
--
Daniel

Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is
to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I want
to
do a formula in the CF formula box. The user can change the value in K1
and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built as the
result of an expression.

Any idea? Thank you.
--
Daniel

Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
-----------------------------



"Charabeuh" a écrit dans le message de
...
Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is
to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional
operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I
want to
do a formula in the CF formula box. The user can change the value in K1
and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built as
the
result of an expression.

Any idea? Thank you.
--
Daniel

too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
-----------------------




"Charabeuh" a écrit dans le message de
...
Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
-----------------------------



"Charabeuh" a écrit dans le message de
...
Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where
the
value is higher than the value in K1. Good. Now what I was asked to do
is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional
operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I
want to
do a formula in the CF formula box. The user can change the value in K1
and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built as
the
result of an expression.

Any idea? Thank you.
--
Daniel

Try this:

=AND(COUNT(K$1,B2)=2,IF(K$2="",B2K$1,IF(K$2="<", B2<K$1)))

--
Biff
Microsoft Excel MVP


"dlamarche" <Melbourne wrote in message
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is
to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I want
to
do a formula in the CF formula box. The user can change the value in K1
and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built as the
result of an expression.

Any idea? Thank you.
--
Daniel

xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis


Charabeuh wrote:
too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
-----------------------




"Charabeuh" a écrit dans le message de
...
Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
-----------------------------



"Charabeuh" a écrit dans le message de
...
Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B
where the
value is higher than the value in K1. Good. Now what I was asked to
do is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional
operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and
I want to
do a formula in the CF formula box. The user can change the value in
K1 and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built
as the
result of an expression.

Any idea? Thank you.
--
Daniel

Hello,

As a side note, "=" is usually not required to "Evaluate"
Thanks! I will keep it in mind.

== Why I prefer : x.Address & " " & y.Value & " " & z.Address
instead of : x & y & z

I have a french excel 2007 and use the "," for decimal numbers
if x=3,5 and y =< and z = 5 then x & y & z gives : 3,5<5
When VBA evaluate this, the result is an erreur of value.
VBA only understand american forms of decimals.
When the formula uses addresses the result is right.
the formula becomes : A1 < C1 that is correctly evaluated.
This formula should work with either the '.' or ',' for decimal numbers.

I could also change my regional options to use the '.' instead
of ",' for decimals to use Evaluate(Join(v))





"Dana DeLouis" a écrit dans le message de
...
xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis


Charabeuh wrote:
too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
-----------------------




"Charabeuh" a écrit dans le message de
...
Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
-----------------------------



"Charabeuh" a écrit dans le message de
...
Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B where
the
value is higher than the value in K1. Good. Now what I was asked to do
is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional
operator
too which, say, is in K2. So K1 contains 3500 and K2 contains and I
want to
do a formula in the CF formula box. The user can change the value in
K1 and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation
like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one built as
the
result of an expression.

Any idea? Thank you.
--
Daniel

I have a french excel 2007 and use the "," for decimal numbers
When the formula uses addresses the result is right.

Thanks. I learned something new. :)

Dana


Charabeuh wrote:
Hello,

As a side note, "=" is usually not required to "Evaluate"
Thanks! I will keep it in mind.

== Why I prefer : x.Address & " " & y.Value & " " & z.Address
instead of : x & y & z

I have a french excel 2007 and use the "," for decimal numbers
if x=3,5 and y =< and z = 5 then x & y & z gives : 3,5<5
When VBA evaluate this, the result is an erreur of value.
VBA only understand american forms of decimals.
When the formula uses addresses the result is right.
the formula becomes : A1 < C1 that is correctly evaluated.
This formula should work with either the '.' or ',' for decimal numbers.

I could also change my regional options to use the '.' instead
of ",' for decimals to use Evaluate(Join(v))





"Dana DeLouis" a écrit dans le message de
...
xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis


Charabeuh wrote:
too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
-----------------------




"Charabeuh" a écrit dans le message de
...
Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
-----------------------------



"Charabeuh" a écrit dans le message de
...
Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example
$K$2 is for example 3500



"dlamarche" <Melbourne a écrit dans le message de
...
Hello I know that I can do a formula like:

$B2$K$1 in the formula box to apply a DF all cells in column B
where the
value is higher than the value in K1. Good. Now what I was asked
to do is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional
operator
too which, say, is in K2. So K1 contains 3500 and K2 contains
and I want to
do a formula in the CF formula box. The user can change the value
in K1 and
type or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation
like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=45006000

I have played with indirect a bit to create a reference in a
concatenation.
It seems that CF can only accept a real formula and not a one
built as the
result of an expression.

Any idea? Thank you.
--
Daniel