1. Enters an Excel Code Module :-

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

cell.Activate 'How could this statement be eliminated ?

If Evaluate(cell.FormatConditions(1).Formula1) Then
MsgBox "FormatConditions = True"
End If
End If
Next cell

2. Would like to maintain the functionality of the above code after
removing the statement, "cell.Activate".
And so, that means that certain other replacement code must be
emplaced thereof.

3. The primary objection to "cell.Activate" is that it causes screen-
blinking and it's still blink-blink-blink of the screen despite that,

Application.ScreenUpdating = False :cell.Activate

is emplaced thereof.

4. Please share your experience. Regards.

Dim cell As Range
Dim sF1 As String

For Each cell In ActiveSheet.Range("B6:B8") 'N145")

If cell.FormatConditions.Count 0 Then

're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
sF1 = .Substitute(cell.FormatConditions(1).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 = True"
End If
End If
Next cell


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
1. Enters an Excel Code Module :-

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

cell.Activate 'How could this statement be eliminated ?

If Evaluate(cell.FormatConditions(1).Formula1) Then
MsgBox "FormatConditions = True"
End If
End If
Next cell

2. Would like to maintain the functionality of the above code after
removing the statement, "cell.Activate".
And so, that means that certain other replacement code must be
emplaced thereof.

3. The primary objection to "cell.Activate" is that it causes screen-
blinking and it's still blink-blink-blink of the screen despite that,

Application.ScreenUpdating = False :cell.Activate

is emplaced thereof.

4. Please share your experience. Regards.

Oops, I adjusted the range to test

Dim cell As Range
Dim sF1 As String

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
sF1 = .Substitute(cell.FormatConditions(1).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 = True"
End If
End If
Next cell


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
1. Enters an Excel Code Module :-

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

cell.Activate 'How could this statement be eliminated ?

If Evaluate(cell.FormatConditions(1).Formula1) Then
MsgBox "FormatConditions = True"
End If
End If
Next cell

2. Would like to maintain the functionality of the above code after
removing the statement, "cell.Activate".
And so, that means that certain other replacement code must be
emplaced thereof.

3. The primary objection to "cell.Activate" is that it causes screen-
blinking and it's still blink-blink-blink of the screen despite that,

Application.ScreenUpdating = False :cell.Activate

is emplaced thereof.

4. Please share your experience. Regards.

Taking it one step further, test all conditions not just the first

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)

're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" wrote in message
...
Oops, I adjusted the range to test

Dim cell As Range
Dim sF1 As String

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
sF1 = .Substitute(cell.FormatConditions(1).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 = True"
End If
End If
Next cell


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



wrote in message
...
1. Enters an Excel Code Module :-

For Each cell In ActiveSheet.Range("B6:N145")

If cell.FormatConditions.Count 0 Then

cell.Activate 'How could this statement be eliminated ?

If Evaluate(cell.FormatConditions(1).Formula1) Then
MsgBox "FormatConditions = True"
End If
End If
Next cell

2. Would like to maintain the functionality of the above code after
removing the statement, "cell.Activate".
And so, that means that certain other replacement code must be
emplaced thereof.

3. The primary objection to "cell.Activate" is that it causes screen-
blinking and it's still blink-blink-blink of the screen despite that,

Application.ScreenUpdating = False :cell.Activate

is emplaced thereof.

4. Please share your experience. Regards.

Mr. Bob Phillips,

Thank you for clarification of my query.

1. It appears that should FC.Formula1contain a Named Formula, the
following code would not accomodate the situation.

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. Please share your experience. Regards.

I am not experiencing that. If I create a CF with a named formula, and the
condition is met, that code finds it just fine for me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
...
Mr. Bob Phillips,

Thank you for clarification of my query.

1. It appears that should FC.Formula1contain a Named Formula, the
following code would not accomodate the situation.

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. Please share your experience. Regards.

Mr. Bob Phillips,

Thank you.

1. The code runs as follows :-
_______________________________________________

sF1 = .Substitute(FC.Formula1, "ROW()", cell.Row)
sF1 = .Substitute(sF1, "COLUMN()", cell.Column)

sF1 = .ConvertFormula(sF1, xlA1, xlR1C1) 'This
statement would cause error unless, "Dim SF1 as Variant" (just like
the Help File has shown) instead of "Dim sF1 as String".

sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
_______________________________________________

2. And then, the code would run until "Evaluate(sF1)" fails ...... Is
it due to "Dim sF1 as Variant" ?

3. This is Excel 2000.

Regards.

ConvertFormula might return a Variant result, but it is sub-type string, a
formula is a string, so there is absolutely nothing wrong in declaring SF1
as string, it will not error if you do. Variant will work, but it is not
necessary.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
Mr. Bob Phillips,

Thank you.

1. The code runs as follows :-
_______________________________________________

sF1 = .Substitute(FC.Formula1, "ROW()", cell.Row)
sF1 = .Substitute(sF1, "COLUMN()", cell.Column)

sF1 = .ConvertFormula(sF1, xlA1, xlR1C1) 'This
statement would cause error unless, "Dim SF1 as Variant" (just like
the Help File has shown) instead of "Dim sF1 as String".

sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
_______________________________________________

2. And then, the code would run until "Evaluate(sF1)" fails ...... Is
it due to "Dim sF1 as Variant" ?

3. This is Excel 2000.

Regards.

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(D51,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(D51,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.