This issue seems to be tricky: out of a huge EXCEL file containing roughly 6'000 cells including a conditional formatting, I want to scan all those cells and report in a separate file the location and conditional formula in the R1C1 format included in the appropriate conditional formatting. In a second step I would then transfer the found conditional formattings to a new workbook. For this, I require the conditional formatting formula in the R1C1 format.

Example: in cell G77 there is conditional formatting built in as shown below, result in the immediate pane is:

[code]
? range("G77").FormatConditions(1).Formula1
=IF(G76<>"";IF(AND(G77<>"O";G77<>"I";G77<>"H";G77<>"T");TRUE;FALSE);FALSE)
/[code]

So, the formula includes not only semicolons but also quotation marks which causes the following conversion statement to fail:

[code]
? Application.ConvertFormula(range("G77").FormatConditions(1).Formula1, xlA1, xlR1C1, xlAbsolute)
Error 2015
/[code]

OK, so far so fine. I looks like I have to double the quotation marks and replace the semicolons with a comma in order to convert the formula first in a accepted format. I have written the functions CFFormulaConvertToR1C1 and DuplicateQuote to do this:

[code]
Public Function DuplicateQuote(MyString) As String
Dim TempString As String, NewString As String, TempChar As String, Counter As Integer
If Len(MyString) = 0 Or IsNull(MyString) Then
DuplicateQuote = ""
Exit Function
End If
' Duplicate Hochkommas
TempString = ""
For Counter = 1 To Len(MyString)
TempChar = Mid$(MyString, Counter, 1)
If Asc(TempChar) = 34 Then
TempString = TempString & TempChar & Chr(34)
Else
TempString = TempString & TempChar
End If
Next
DuplicateQuote = TempString
End Function

Public Function CFFormulaConvertToR1C1(MyFormula)
' MyFormula:original formula in A1 format
Dim NewFormula 'original formula in A1 format
Debug.Print "Original Formula: " & MyFormula
NewFormula = DuplicateQuote(MyFormula)
Debug.Print "DuplicateQuote : " & NewFormula

NewFormula = Replace(NewFormula, ";", ",")
Debug.Print "ReplaceSemiCol : " & NewFormula

Debug.Print Application.ConvertFormula(NewFormula, xlA1, xlR1C1, xlAbsolute) 'FAILS!

NewFormula = Application.ConvertFormula(NewFormula, xlA1, xlR1C1, xlAbsolute) 'FAILS!
CFFormulaConvertToR1C1 = NewFormula
End Function
/[code]

No comes the strange thing: the formula is now converted into the correct format, which is approved by the conversion to the R1C1 format in the immediate pane. But why does the same function call fail if called by a VBA routine?

[code]
? Application.ConvertFormula("=IF(G76<>"""",IF(AND(G77<>""O"",G77<>""I"",G77<>""H"",G77<>""T""),TRUE,FALSE),FALSE)", xlA1, xlR1C1, xlAbsolute)
=IF(R76C7<>"",IF(AND(R77C7<>"O",R77C7<>"I",R77C7<>"H",R77C7<>"T"),TRUE,FALSE),FALSE) 'WORKS in immediate pane!

? CFFormulaConvertToR1C1(range("G77").FormatConditions(1).Formula1)
Original Formula: =IF(G76<>"";IF(AND(G77<>"O";G77<>"I";G77<>"H";G77<>"T");TRUE;FALSE);FALSE)
DuplicateQuote : =IF(G76<>"""";IF(AND(G77<>""O"";G77<>""I"";G77<>""H"";G77<>""T"");TRUE;FALSE);FALSE)
ReplaceSemiCol : =IF(G76<>"""",IF(AND(G77<>""O"",G77<>""I"",G77<>""H"",G77<>""T""),TRUE,FALSE),FALSE)
Error 2015
Error 2015
/[code]

Thanks already in advance for any tips!