Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |