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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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(OMS Z));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" wrote in message
...
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" wrote in message
...
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