Conditional formatting
 

I need a function that, given a range (single cell) as an argument, will
determine if the cell has been conditionally formatted with EquationIs and,
if yes, return the first formula as a text string. I tried:

Function mnb(r As Range) As String
mnb = r.FormatConditions(1).Formula1
End Function

This almost works, but it returns equations with cell references translated
to the location of the function call rather than the range in the call. For
example, if the conditional formatting in C1 is formula =(C110) then
=mnb(C1) should display =(C110), instead it is displaying =(Z10010) if
=mnb(C1) happens to be in Z100.

As a sub there is no problem:

Sub macro3()
Dim r As Range
Dim s As String
Set r = Selection
s = r.FormatConditions(1).Formula1
MsgBox (s)
End Sub

Thanks in Advance

--
Gary''s Student gsnu

Conditional formatting
 

I don't know if this is a general solution, but try this:

Function mnb(r As Range) As String
Dim s As String, s1 As String, s2 As String
s = r.FormatConditions(1).Formula1
s1 = Application.ConvertFormula(s, xlA1, xlR1C1, , ActiveCell)
s2 = Application.ConvertFormula(s1, xlR1C1, xlA1, , r)
mnb = s2
End Function

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
I need a function that, given a range (single cell) as an argument, will
determine if the cell has been conditionally formatted with EquationIs
and,
if yes, return the first formula as a text string. I tried:

Function mnb(r As Range) As String
mnb = r.FormatConditions(1).Formula1
End Function

This almost works, but it returns equations with cell references
translated
to the location of the function call rather than the range in the call.
For
example, if the conditional formatting in C1 is formula =(C110) then
=mnb(C1) should display =(C110), instead it is displaying =(Z10010) if
=mnb(C1) happens to be in Z100.

As a sub there is no problem:

Sub macro3()
Dim r As Range
Dim s As String
Set r = Selection
s = r.FormatConditions(1).Formula1
MsgBox (s)
End Sub

Thanks in Advance

--
Gary''s Student gsnu

Conditional formatting
 

Your solution works just fine.

Thanks

If I could trouble you a little more, what was wrong with what I was trying??
--
Gary's Student


"Tom Ogilvy" wrote:

I don't know if this is a general solution, but try this:

Function mnb(r As Range) As String
Dim s As String, s1 As String, s2 As String
s = r.FormatConditions(1).Formula1
s1 = Application.ConvertFormula(s, xlA1, xlR1C1, , ActiveCell)
s2 = Application.ConvertFormula(s1, xlR1C1, xlA1, , r)
mnb = s2
End Function

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
I need a function that, given a range (single cell) as an argument, will
determine if the cell has been conditionally formatted with EquationIs
and,
if yes, return the first formula as a text string. I tried:

Function mnb(r As Range) As String
mnb = r.FormatConditions(1).Formula1
End Function

This almost works, but it returns equations with cell references
translated
to the location of the function call rather than the range in the call.
For
example, if the conditional formatting in C1 is formula =(C110) then
=mnb(C1) should display =(C110), instead it is displaying =(Z10010) if
=mnb(C1) happens to be in Z100.

As a sub there is no problem:

Sub macro3()
Dim r As Range
Dim s As String
Set r = Selection
s = r.FormatConditions(1).Formula1
MsgBox (s)
End Sub

Thanks in Advance

--
Gary''s Student gsnu

Conditional formatting
 

It doesn't work.

--
Regards,
Tom Ogilvy

"Gary''s Student" wrote in message
...
Your solution works just fine.

Thanks

If I could trouble you a little more, what was wrong with what I was
trying??
--
Gary's Student


"Tom Ogilvy" wrote:

I don't know if this is a general solution, but try this:

Function mnb(r As Range) As String
Dim s As String, s1 As String, s2 As String
s = r.FormatConditions(1).Formula1
s1 = Application.ConvertFormula(s, xlA1, xlR1C1, , ActiveCell)
s2 = Application.ConvertFormula(s1, xlR1C1, xlA1, , r)
mnb = s2
End Function

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in
message
...
I need a function that, given a range (single cell) as an argument,
will
determine if the cell has been conditionally formatted with EquationIs
and,
if yes, return the first formula as a text string. I tried:

Function mnb(r As Range) As String
mnb = r.FormatConditions(1).Formula1
End Function

This almost works, but it returns equations with cell references
translated
to the location of the function call rather than the range in the
call.
For
example, if the conditional formatting in C1 is formula =(C110) then
=mnb(C1) should display =(C110), instead it is displaying =(Z10010)
if
=mnb(C1) happens to be in Z100.

As a sub there is no problem:

Sub macro3()
Dim r As Range
Dim s As String
Set r = Selection
s = r.FormatConditions(1).Formula1
MsgBox (s)
End Sub

Thanks in Advance

--
Gary''s Student gsnu