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 =(C1>10) then
=mnb(C1) should display =(C1>10), instead it is displaying =(Z100>10) 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 =(C1>10) then
> =mnb(C1) should display =(C1>10), instead it is displaying =(Z100>10) 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 =(C1>10) then
> > =mnb(C1) should display =(C1>10), instead it is displaying =(Z100>10) 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
>
>
>

It doesn't work.

--
Regards,
Tom Ogilvy

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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 =(C1>10) then
> > > =mnb(C1) should display =(C1>10), instead it is displaying =(Z100>10)
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
> >
> >
> >