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.