FormatConditions collection
Dick Kusleika
range whose format condition is true. I started with this function.
i = 0
For Each cell In Selection
Range("D31").Formula = cell.FormatConditions(1).Formula1
Calculate 'I don't think I need this but I was desparate
If Range("D31").Value = True Then
i = i + 1
End If
Next cell
Range("D31").Value = i
This is actually a sub (not a function) for debugging purposes. It was not
returning the correct result, so I wrote this sub
Sub Macro_2()
MsgBox Range("D3").FormatConditions(1).Formula1
Msgbox Range("D4").FormatConditions(1).Formula1
End Sub
The two message boxes returned the exact same formula. Here's the kicker.
Neither of the formulas was correct. The actual formula for D3 is
=ISERROR(MATCH(D3,Win1,0)) and the one for D4 is similar just testing D4
instead of D3.
The message box returned =ISERROR(MATCH(G15,Win1,0)) The active cell when I
ran this sub was G15. G15 does not contain any conditional formatting.
Changing the active cell and running the sub gave the message above but with
the active cell address in place of G15.
I suspect my problem is with FormatConditons(1). I tried
FormatConditions.Item(1) but got the same results. When I wrote a sub that
looked like this
MsgBox ActiveCell.FormatConditions(1).Formula1
It worked fine. Or at least gave me the correct conditional formatting
formula for the active cell.
How do I return the formula in the format condition for each cell by looping
through a range of cells? Any insights will be greatly appreciated.
Thanks
Dick K.
John Green
You are running into a conceptualisation problem that is familiar to those of
us old enough to have used the xlm or Excel 4 macro language, where the
following concept of relative referencing was universal.
Relative references in conditional formulas are always reported relative to
the active cell. Your formula will only be correct when the cell containing
the conditional formula is active. This makes it difficult to write a
worksheet function that can evaluate the conditional formats in a range of
cells, as a worksheet function can't change the active cell position.
I suggest that you test the format of the cells, which has been determined by
the conditional format. If you know that the cells that meet the condition are
red, count the red cells, for example.
If you really need to extract the conditional formulas, you will need to
convert any relative cell references by adjusting them according to the active
cell position,
HTH,
John Green (Excel MVP)
Sydney
Australia
Please post all replies to NewsGroups
In article <_Ofr5.49933$Pi.14...@nntp3.onemain.com>, Dick Kusleika wrote:
> From: "Dick Kusleika" <par...@radiksns.net>
> Newsgroups: microsoft.public.excel.programming
> Subject: FormatConditions collection
> Date: Wed, 30 Aug 2000 17:23:07 -0500
Dick Kusleika
> Relative references in conditional formulas are always reported relative
to
> the active cell. Your formula will only be correct when the cell
containing
> the conditional formula is active. This makes it difficult to write a
> worksheet function that can evaluate the conditional formats in a range of
> cells, as a worksheet function can't change the active cell position.
>
Is this true only of conditional format formulas, or are there other areas
where I might see this behavior?
> I suggest that you test the format of the cells, which has been determined
by
> the conditional format. If you know that the cells that meet the condition
are
> red, count the red cells, for example.
>
I tried that first, and all of the .font.colorindexes came up as something
like -4503 (whether the condition was true or not) which I assume is the
same as xlautomatic. Chip's webpage confirmed that conditional formatting
does not change the colorindex property of a cell.
> If you really need to extract the conditional formulas, you will need to
> convert any relative cell references by adjusting them according to the
active
> cell position,
>
So if my active cell is going to be D31, then I would have to make the
conditional format formula in D30:
ISERROR(MATCH(OFFSET(D31,-1,0),Win1,0)). Is that what you mean?
Thank you for taking the time to respond
Dick K.
John Green
Dick,
Sorry, I should have tested my suggestion. So, here's a technique that you
might be able to use to count the conditional formats that are True. You need
to be careful with it. Any absolute cell references in your conditional format
need to be in the form of names, just like Win1 in your formula:
=ISERROR(MATCH(D4,Win1,0))
Absolute references, such as $A$1:$A$10 will be garbaged by my code. Names are
not affected. The CountCF function returns a count of the Conditional formats
that evaluate as True in the input range. It ignores cells with no conditional
format. EvalCF uses the ConvertFormula method to convert the conditional
formula so that it is relative to the conditional format cell.
I find that CountCF works OK when you first enter it. It can give the wrong
result if a conditional format cell changes. Ctrl+Alt+F9 does not force a
correct calculation, but moving the cell causes it to evaluate correctly.
Clearly, more work needs to be done, but this should give you something to go
on.
Function CountCF(rngIn As Range) As Integer
Application.Volatile
Dim rng As Range
Dim rngRelative As Range
Dim intCount As Integer
Dim val As Variant
Set rngRelative = Application.Caller
For Each rng In rngIn
val = EvalCF(rng, rngRelative)
If Not IsError(val) Then
If val Then
intCount = intCount + 1
End If
End If
Next rng
CountCF = intCount
End Function
Function EvalCF(rng As Range, rngRelative As Range)
Dim strFormula As String
On Error Resume Next
strFormula = rng.FormatConditions(1).Formula1
If Err.Number <> 0 Then
EvalCF = CVErr(xlErrNA)
Else
strFormula = Application.ConvertFormula( _
Formula:=strFormula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlR1C1, _
ToAbsolute:=xlRelative, _
RelativeTo:=rngRelative)
strFormula = Application.ConvertFormula( _
Formula:=strFormula, _
FromReferenceStyle:=xlR1C1, _
ToReferenceStyle:=xlA1, _
ToAbsolute:=xlRelative, _
RelativeTo:=rng)
EvalCF = Application.Evaluate(strFormula)
End If
End Function
John Green (Excel MVP)
Sydney
Australia
Please post all replies to NewsGroups
In article <aOsr5.743$kI2....@nntp1.onemain.com>, Dick Kusleika wrote:
> From: "Dick Kusleika" <par...@radiksns.net>
> Newsgroups: microsoft.public.excel.programming
> Subject: Re: FormatConditions collection
> Date: Thu, 31 Aug 2000 08:09:11 -0500