FormatConditions(1).Formula1

Hi All,

It is known that
ActiveCell.FormatConditions(1).Formula1
returns the formula as a string.

How can I ask if this formula fulfils for ActiveCell (or another cell) or not?

Thanks,
Stefi

I had an idea: place ActiveCell.FormatConditions(1).Formula1 into an unused
cell and let XL evaluate it, but it failed because of my using a national
language version of XL2003 (Hungarian).
ActiveCell.FormatConditions(1).Formula1 returns the formula in Hungarian
unlike a normal ActiveCell.Formula that returns the formula translated into
English. When placing ActiveCell.Formula back into another cell XL
re-translate it into Hungarian and it works well. But when trying to place
ActiveCell.FormatConditions(1).Formula1 into an unused cell, XL tries to
re-translate the Hungarian text (XL expects it to be English) into Hungarian
and , of course, it fails!

Any idea?

Stefi


„Stefi” ezt �*rta:

> Hi All,
>
> It is known that
> ActiveCell.FormatConditions(1).Formula1
> returns the formula as a string.
>
> How can I ask if this formula fulfils for ActiveCell (or another cell) or not?
>
> Thanks,
> Stefi
>

Pardon? What does the question mean?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> It is known that
> ActiveCell.FormatConditions(1).Formula1
> returns the formula as a string.
>
> How can I ask if this formula fulfils for ActiveCell (or another cell) or
not?
>
> Thanks,
> Stefi
>

Hi Bob,

Sorry for my poor English! I'd like to know wether the formula in
ActiveCell.FormatConditions(1).Formula1 gives True or False!

In the meantime I made a solution:

Function FormCondTF(fcformulaLoc, workcell)
Range(workcell).FormulaLocal = fcformulaLoc
FormCondTF = Range(workcell)
Range(workcell).ClearContents
End Function

Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")

But I'm still interested in your opinion!

Regards,
Stefi

„Bob Phillips” ezt �*rta:

> Pardon? What does the question mean?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi All,
> >
> > It is known that
> > ActiveCell.FormatConditions(1).Formula1
> > returns the formula as a string.
> >
> > How can I ask if this formula fulfils for ActiveCell (or another cell) or
> not?
> >
> > Thanks,
> > Stefi
> >
>
>
>

Check out Chip Pearson's page on this topic:

http://www.cpearson.com/excel/CFColors.htm

For your function,
Depending on your formula, I am not sure it is a general solution.

--
Regards,
Tom Ogilvy

"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Sorry for my poor English! I'd like to know wether the formula in
> ActiveCell.FormatConditions(1).Formula1 gives True or False!
>
> In the meantime I made a solution:
>
> Function FormCondTF(fcformulaLoc, workcell)
> Range(workcell).FormulaLocal = fcformulaLoc
> FormCondTF = Range(workcell)
> Range(workcell).ClearContents
> End Function
>
> Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
>
> But I'm still interested in your opinion!
>
> Regards,
> Stefi
>
> "Bob Phillips" ezt �rta:
>
> > Pardon? What does the question mean?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Stefi" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi All,
> > >
> > > It is known that
> > > ActiveCell.FormatConditions(1).Formula1
> > > returns the formula as a string.
> > >
> > > How can I ask if this formula fulfils for ActiveCell (or another cell)
or
> > not?
> > >
> > > Thanks,
> > > Stefi
> > >
> >
> >
> >

If I understand you correctly, you want to know if a cell is meeting its CF
conditions. This is problematical if that cell is not active. This is what I
use

'---------------------------------------------------------------------
Public Function IsCFMet(rng As Range) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count > 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
IsCFMet = rng.Value = oFC.Formula1
Case xlNotEqual
IsCFMet = rng.Value <> oFC.Formula1
Case xlGreater
IsCFMet = rng.Value > oFC.Formula1
Case xlGreaterEqual
IsCFMet = rng.Value >= oFC.Formula1
Case xlLess
IsCFMet = rng.Value < oFC.Formula1
Case xlLessEqual
IsCFMet = rng.Value <= oFC.Formula1
IsCFMet = (rng.Value >= oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
IsCFMet = (rng.Value < oFC.Formula1 Or _
rng.Value > oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
IsCFMet = rng.Parent.Evaluate(sF1)
End If
If IsCFMet Then Exit Function
Next oFC
End If 'rng.FormatConditions.Count > 0

End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Sorry for my poor English! I'd like to know wether the formula in
> ActiveCell.FormatConditions(1).Formula1 gives True or False!
>
> In the meantime I made a solution:
>
> Function FormCondTF(fcformulaLoc, workcell)
> Range(workcell).FormulaLocal = fcformulaLoc
> FormCondTF = Range(workcell)
> Range(workcell).ClearContents
> End Function
>
> Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
>
> But I'm still interested in your opinion!
>
> Regards,
> Stefi
>
> "Bob Phillips" ezt �rta:
>
> > Pardon? What does the question mean?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Stefi" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi All,
> > >
> > > It is known that
> > > ActiveCell.FormatConditions(1).Formula1
> > > returns the formula as a string.
> > >
> > > How can I ask if this formula fulfils for ActiveCell (or another cell)
or
> > not?
> > >
> > > Thanks,
> > > Stefi
> > >
> >
> >
> >

Thanks Tom,

I know that my formula is applicable only for a simple case, but for the
moment I have only that simple case, and I was glad to solve my present
problem.

Many thanks for your guiding me to Chip's page, now I have an abundant
choice of solutions, including Bob's one, I am going to test them, paying
special attention to handling FormulaLocal, because
ActiveCell.FormatConditions(1).Formula1 returns formulae in Local format in
national language versions.

Regards,
Stefi


„Tom Ogilvy” ezt �*rta:

> Check out Chip Pearson's page on this topic:
>
> http://www.cpearson.com/excel/CFColors.htm
>
> For your function,
> Depending on your formula, I am not sure it is a general solution.
>
> --
> Regards,
> Tom Ogilvy
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > Sorry for my poor English! I'd like to know wether the formula in
> > ActiveCell.FormatConditions(1).Formula1 gives True or False!
> >
> > In the meantime I made a solution:
> >
> > Function FormCondTF(fcformulaLoc, workcell)
> > Range(workcell).FormulaLocal = fcformulaLoc
> > FormCondTF = Range(workcell)
> > Range(workcell).ClearContents
> > End Function
> >
> > Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
> >
> > But I'm still interested in your opinion!
> >
> > Regards,
> > Stefi
> >
> > "Bob Phillips" ezt �*rta:
> >
> > > Pardon? What does the question mean?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Stefi" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi All,
> > > >
> > > > It is known that
> > > > ActiveCell.FormatConditions(1).Formula1
> > > > returns the formula as a string.
> > > >
> > > > How can I ask if this formula fulfils for ActiveCell (or another cell)
> or
> > > not?
> > > >
> > > > Thanks,
> > > > Stefi
> > > >
> > >
> > >
> > >
>
>
>

Hi Bob,

My question was exactly what you understood! Many thanks for your reply, it
takes some time to understand and apply it, I will let you know the result.

Regards,
Stefi

„Bob Phillips” ezt �*rta:

> If I understand you correctly, you want to know if a cell is meeting its CF
> conditions. This is problematical if that cell is not active. This is what I
> use
>
> '---------------------------------------------------------------------
> Public Function IsCFMet(rng As Range) As Boolean
> '---------------------------------------------------------------------
> Dim oFC As FormatCondition
> Dim sF1 As String
> Dim iRow As Long
> Dim iColumn As Long
>
> Set rng = rng(1, 1)
> If rng.FormatConditions.Count > 0 Then
> For Each oFC In rng.FormatConditions
> If oFC.Type = xlCellValue Then
> Select Case oFC.Operator
> Case xlEqual
> IsCFMet = rng.Value = oFC.Formula1
> Case xlNotEqual
> IsCFMet = rng.Value <> oFC.Formula1
> Case xlGreater
> IsCFMet = rng.Value > oFC.Formula1
> Case xlGreaterEqual
> IsCFMet = rng.Value >= oFC.Formula1
> Case xlLess
> IsCFMet = rng.Value < oFC.Formula1
> Case xlLessEqual
> IsCFMet = rng.Value <= oFC.Formula1
> IsCFMet = (rng.Value >= oFC.Formula1 And _
> rng.Value <= oFC.Formula2)
> Case xlNotBetween
> IsCFMet = (rng.Value < oFC.Formula1 Or _
> rng.Value > oFC.Formula2)
> End Select
> Else
> 're-adjust the formula back to the formula that applies
> 'to the cell as relative formulae adjust to the activecell
> With Application
> iRow = rng.Row
> iColumn = rng.Column
> sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
> sF1 = .Substitute(sF1, "COLUMN()", iColumn)
> sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
> sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
> End With
> IsCFMet = rng.Parent.Evaluate(sF1)
> End If
> If IsCFMet Then Exit Function
> Next oFC
> End If 'rng.FormatConditions.Count > 0
>
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > Sorry for my poor English! I'd like to know wether the formula in
> > ActiveCell.FormatConditions(1).Formula1 gives True or False!
> >
> > In the meantime I made a solution:
> >
> > Function FormCondTF(fcformulaLoc, workcell)
> > Range(workcell).FormulaLocal = fcformulaLoc
> > FormCondTF = Range(workcell)
> > Range(workcell).ClearContents
> > End Function
> >
> > Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
> >
> > But I'm still interested in your opinion!
> >
> > Regards,
> > Stefi
> >
> > "Bob Phillips" ezt �*rta:
> >
> > > Pardon? What does the question mean?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Stefi" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi All,
> > > >
> > > > It is known that
> > > > ActiveCell.FormatConditions(1).Formula1
> > > > returns the formula as a string.
> > > >
> > > > How can I ask if this formula fulfils for ActiveCell (or another cell)
> or
> > > not?
> > > >
> > > > Thanks,
> > > > Stefi
> > > >
> > >
> > >
> > >
>
>
>

Hi Bob,

I tried your macro and it failed in my Hungarian XL version at line
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
because sF1 contained
=HIBÁS(HOL.VAN($D6;OFSZET(OMSZ;0;0;1;OSZLOPOK(OMSZ));0))
It stands for
=ISERROR(MATCH($D6,OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ)),0))
so it should be converted into the English format to make it applicable.

The formula can be converted through a helper cell:
Range("helperCell").Formulalocal="=HIBÁS(HOL.VAN($D6;OFSZET(OMSZ;0;0;1;OSZLOPOK(OMSZ));0))"
ConvertedFormula = Range("helperCell").Formula

Is there a simpler way (without a helper cell)?

Regards,
Stefi



„Bob Phillips” ezt �*rta:

> If I understand you correctly, you want to know if a cell is meeting its CF
> conditions. This is problematical if that cell is not active. This is what I
> use
>
> '---------------------------------------------------------------------
> Public Function IsCFMet(rng As Range) As Boolean
> '---------------------------------------------------------------------
> Dim oFC As FormatCondition
> Dim sF1 As String
> Dim iRow As Long
> Dim iColumn As Long
>
> Set rng = rng(1, 1)
> If rng.FormatConditions.Count > 0 Then
> For Each oFC In rng.FormatConditions
> If oFC.Type = xlCellValue Then
> Select Case oFC.Operator
> Case xlEqual
> IsCFMet = rng.Value = oFC.Formula1
> Case xlNotEqual
> IsCFMet = rng.Value <> oFC.Formula1
> Case xlGreater
> IsCFMet = rng.Value > oFC.Formula1
> Case xlGreaterEqual
> IsCFMet = rng.Value >= oFC.Formula1
> Case xlLess
> IsCFMet = rng.Value < oFC.Formula1
> Case xlLessEqual
> IsCFMet = rng.Value <= oFC.Formula1
> IsCFMet = (rng.Value >= oFC.Formula1 And _
> rng.Value <= oFC.Formula2)
> Case xlNotBetween
> IsCFMet = (rng.Value < oFC.Formula1 Or _
> rng.Value > oFC.Formula2)
> End Select
> Else
> 're-adjust the formula back to the formula that applies
> 'to the cell as relative formulae adjust to the activecell
> With Application
> iRow = rng.Row
> iColumn = rng.Column
> sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
> sF1 = .Substitute(sF1, "COLUMN()", iColumn)
> sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
> sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
> End With
> IsCFMet = rng.Parent.Evaluate(sF1)
> End If
> If IsCFMet Then Exit Function
> Next oFC
> End If 'rng.FormatConditions.Count > 0
>
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > Sorry for my poor English! I'd like to know wether the formula in
> > ActiveCell.FormatConditions(1).Formula1 gives True or False!
> >
> > In the meantime I made a solution:
> >
> > Function FormCondTF(fcformulaLoc, workcell)
> > Range(workcell).FormulaLocal = fcformulaLoc
> > FormCondTF = Range(workcell)
> > Range(workcell).ClearContents
> > End Function
> >
> > Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
> >
> > But I'm still interested in your opinion!
> >
> > Regards,
> > Stefi
> >
> > "Bob Phillips" ezt �*rta:
> >
> > > Pardon? What does the question mean?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Stefi" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi All,
> > > >
> > > > It is known that
> > > > ActiveCell.FormatConditions(1).Formula1
> > > > returns the formula as a string.
> > > >
> > > > How can I ask if this formula fulfils for ActiveCell (or another cell)
> or
> > > not?
> > > >
> > > > Thanks,
> > > > Stefi
> > > >
> > >
> > >
> > >
>
>
>

Hi Tom,

I tried Chip's macro and it failed because of not handling FormulaLocal
(just like Bob's one)!

Regards,
Stefi



„Stefi” ezt �*rta:

> Thanks Tom,
>
> I know that my formula is applicable only for a simple case, but for the
> moment I have only that simple case, and I was glad to solve my present
> problem.
>
> Many thanks for your guiding me to Chip's page, now I have an abundant
> choice of solutions, including Bob's one, I am going to test them, paying
> special attention to handling FormulaLocal, because
> ActiveCell.FormatConditions(1).Formula1 returns formulae in Local format in
> national language versions.
>
> Regards,
> Stefi
>
>
> „Tom Ogilvy” ezt �*rta:
>
> > Check out Chip Pearson's page on this topic:
> >
> > http://www.cpearson.com/excel/CFColors.htm
> >
> > For your function,
> > Depending on your formula, I am not sure it is a general solution.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Stefi" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > Sorry for my poor English! I'd like to know wether the formula in
> > > ActiveCell.FormatConditions(1).Formula1 gives True or False!
> > >
> > > In the meantime I made a solution:
> > >
> > > Function FormCondTF(fcformulaLoc, workcell)
> > > Range(workcell).FormulaLocal = fcformulaLoc
> > > FormCondTF = Range(workcell)
> > > Range(workcell).ClearContents
> > > End Function
> > >
> > > Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
> > >
> > > But I'm still interested in your opinion!
> > >
> > > Regards,
> > > Stefi
> > >
> > > "Bob Phillips" ezt �*rta:
> > >
> > > > Pardon? What does the question mean?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Stefi" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi All,
> > > > >
> > > > > It is known that
> > > > > ActiveCell.FormatConditions(1).Formula1
> > > > > returns the formula as a string.
> > > > >
> > > > > How can I ask if this formula fulfils for ActiveCell (or another cell)
> > or
> > > > not?
> > > > >
> > > > > Thanks,
> > > > > Stefi
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Hi Tom,

I tried Chip's macro and it failed because of not handling FormulaLocal
(just like Bob's one)!

Regards,
Stefi



„Stefi” ezt �*rta:

> Thanks Tom,
>
> I know that my formula is applicable only for a simple case, but for the
> moment I have only that simple case, and I was glad to solve my present
> problem.
>
> Many thanks for your guiding me to Chip's page, now I have an abundant
> choice of solutions, including Bob's one, I am going to test them, paying
> special attention to handling FormulaLocal, because
> ActiveCell.FormatConditions(1).Formula1 returns formulae in Local format in
> national language versions.
>
> Regards,
> Stefi
>
>
> „Tom Ogilvy” ezt �*rta:
>
> > Check out Chip Pearson's page on this topic:
> >
> > http://www.cpearson.com/excel/CFColors.htm
> >
> > For your function,
> > Depending on your formula, I am not sure it is a general solution.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Stefi" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > Sorry for my poor English! I'd like to know wether the formula in
> > > ActiveCell.FormatConditions(1).Formula1 gives True or False!
> > >
> > > In the meantime I made a solution:
> > >
> > > Function FormCondTF(fcformulaLoc, workcell)
> > > Range(workcell).FormulaLocal = fcformulaLoc
> > > FormCondTF = Range(workcell)
> > > Range(workcell).ClearContents
> > > End Function
> > >
> > > Answer = FormCondTF(ActiveCell.FormatConditions(1).Formula1, "Z1")
> > >
> > > But I'm still interested in your opinion!
> > >
> > > Regards,
> > > Stefi
> > >
> > > "Bob Phillips" ezt �*rta:
> > >
> > > > Pardon? What does the question mean?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Stefi" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi All,
> > > > >
> > > > > It is known that
> > > > > ActiveCell.FormatConditions(1).Formula1
> > > > > returns the formula as a string.
> > > > >
> > > > > How can I ask if this formula fulfils for ActiveCell (or another cell)
> > or
> > > > not?
> > > > >
> > > > > Thanks,
> > > > > Stefi
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >