BSALV
Banned user
- Joined
- Oct 31, 2010
- Messages
- 1,651
- Office Version
- 365
- 2013
- 2007
There is a problem with the formula when you use conditional format as stated in this topic. http://spreadsheetpage.com/index.ph...formula1_property_for_conditional_formatting/
Now I have an additional problem because apparently that formula1 is a FormulaLocal, so I can't use normal formulas. The macro below works correct, but isn't there an easier way to achieve the same thing. The convertformula switches between A1 and R1C1. Is there a same tool between Formula and FormulaLocal ?
Now I have an additional problem because apparently that formula1 is a FormulaLocal, so I can't use normal formulas. The macro below works correct, but isn't there an easier way to achieve the same thing. The convertformula switches between A1 and R1C1. Is there a same tool between Formula and FormulaLocal ?
Code:
Sub Test()
'Formulalocal =REST(SUBTOTAAL(102;$A$1:$A1);2)
'Normal Formula =MOD(SUBTOTAL(102,$A$1:$A1),2)
Dim F1 As String, F2 As String, c As Range
Set c = Range("B6") 'certain cell with a conditional format
F1 = c.FormatConditions(1).Formula1 'Read the conditional Formula in c as Formulalocal
Range("AA1").FormulaLocal = F1 'Write it in AA1 (auxiliary cell)
F1 = Range("AA1").Formula 'read the same formula now as normal formula
F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , c) 'convert formula to R1C1 relative to our cell c
F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1")) 'convert formula back to A1
Range("AA1").Formula = F1 'write formula to aux. cell
MsgBox Range("AA1").FormulaLocal 'read formula as FormulaLocal
End Sub