|
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
FormatConditions(1).Formula1
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 |
| All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com