Mr. Bob Phillips",
1. Re-enters the Excel VBA Code as follows :-
Dim cell As Range
Dim sF1 As String
Dim FC As FormatCondition
Dim i As Long
For Each cell In ActiveSheet.Range("B6:N145")
For i = 1 To cell.FormatConditions.Count
Set FC = cell.FormatConditions(i)
With Application
sF1 = .Substitute(FC.Formula1, "ROW()", cell.Row)
sF1 = .Substitute(sF1, "COLUMN()", cell.Column)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
End With
If Evaluate(sF1) Then
MsgBox cell.Address & " - FormatConditions(" & i & ") =
True"
End If
Next i
Next cell
2. In this case, FC.Formula1consists of,
=AND(D5>1,MOD(E5,3)=1,ShtDelta),
where ShtDelta is by Define Name > Refers To,
ShtDelta =
OR((LEFT('Sheet7-Knot'!$C5,6)<>LEFT('Sheet7-Knot'!$C4,6))*
('Sheet7-Knot'!$E5='Sheet7-Knot'!$E4),
(LEFT('Sheet7-Knot'!$C5,6)<>LEFT('Sheet7-Knot'!$C6,6))*
('Sheet7-Knot'!$E5='Sheet7-Knot'!$E6))
There's reservation that,
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
would act on ShtDelta as it is plainly given by,
=AND(D5>1,MOD(E5,3)=1,ShtDelta).
Hence feelingly, there is not any conversion of the formula when the
code runs as is (and the value of ShtDelta obtainable is one evaluated
at the prevalent ActiveCell).
3. When FC.Formula is substituted with ShtDelta (by the full string)
such as,
OR((LEFT('Sheet7-Knot'!$C5,6)<>LEFT('Sheet7-Knot'!$C4,6))*('Sheet7-
Knot'!$E5='Sheet7-Knot'!$E4),
(LEFT('Sheet7-Knot'!$C5,6)<>LEFT('Sheet7-Knot'!$C6,6))*
('Sheet7-Knot'!$E5='Sheet7-Knot'!$E6))
The code runs but halts at Evaluate(sF1) ; the causation of Error is
plainly "Type Mis-Match" of sF1, Period.
4. (Via Inference of Recent Experience) ; There's hope that,
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
should function as one would wish them to be, under the circumstances
that FC.Formula1would be rather Simple (in other words, not to be
complex).
An Ugly Formula in the order of ShtDelta (as given above) and beyond
could well be the causation of an inexplicable Error.
5. Notwithstanding the above, Evaluate(ShtDelta) could be readily
computed in a separate case.
There's evidently computation labour peaking at,
Evaluate(sF1), after it's parsed by Application.ConvertFormula().
6. The code could run with the placement of, cell.Activate ; except
that the resultant screen-blinking was unbearable. It runs in way of
an instance of Coding, manifest of lacking Elegance and, almost
Detrimental of all, less Dignified (the blinking-screen is not unlike
an old tram trundling along the Duchess and Duke Street).
7. Note that,
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
are to be run in tandem for all that intent and purposes of Formula
Conversion (Regardless of one's formula being coded exclusively in
xlA1 Notation).
8. Needless to say, it's painstaking while debugging, akin to the
onset of labour (vs. "Life was Not Meant to be Easy"). However, to
ease labouring, the formula (ShtDelta) was taken apart and,
Evaluate(each Part), separately ; therafter, the solution was
integrated with FC.Formula1.
9. Mr. Bob Phillips, Thank You for giving Guidance in the Way Forward
and once again Thank You for bearing with me all this while. Regards.