Finding cells with active conditional formatting

[Bill Fischette]

I have two worksheets with data organized into 2048 rows of 32 columns each
on each sheet.

I have successfully used conditional formatting to highlight cells where the
value in a cell in the first area differs from the value in the
corresponding cell in the second area by changing the interior color of the
cells.

What I need now is a fast way to determine which cells have been highlighted
by the conditional formatting and the values in these cells in the two
areas. The cells having different values are fairly sparsely located in the
arrays.

I know I can use nested loops and test the interior color of the cells.
Does anyone have a faster way?

Thanks

[John Walkenbach]

This topic was discussed back in May. The conclusion was that there is no
direct way to determine if conditional formatting is in effect for a
particular cell. You can determine if a cell uses conditional formatting,
but there's no simple way to find out if the conditional formatting has
indeed kicked in.

To view the original thread on this topic, search for "Cell's ColorIndex
with Conditional Formatting" at www.deja.com.

----- Posted by John Walkenbach of JWalk & Associates -----
----- Visit "The Spreadsheet Page" -----
----- http://www.j-walk.com/ss -----

Bill Fischette <bfisc...@igsusa.com> wrote in message
news:eTxf688x#GA.94@cppssbbsa05...

[Bill Fischette]

Thanks John,

I scanned the material on deja.com but didn't see where anyone had found any
way to detect active conditional formatting.

It looks as if there is no detectable change in the interior color of the
cell when conditional formatting is active

activecell.Interior.Color gives this for the normal (in this case light
yellow) shading
13434879

activecell.Interior.Color also gives the same result even though the cell
color not appears beige from active conditional formatting
13434879

And like the earlier discussions, I don't care about finding if conditional
formatting is present, only if it is active.

Unless there are any other suggestions, it looks like the only way is to
process each cell in the area and compare it's value with the corresponding
cell in the other area.

Bill

John Walkenbach <jo...@j-walk.com> wrote in message
news:eIUE9r9x#GA....@cppssbbsa02.microsoft.com...

[John Walkenbach]

I think I'm going to start compiling a list flaws in the Excel object model.
The inability to determine if conditional formatting is in effect definitely
qualifies for the list.

-John

Bill Fischette <bfisc...@igsusa.com> wrote in message

news:#IIBPQ#x#GA.70@cppssbbsa03...

[Bill Fischette]

John,

Just a thought. We need to be able to look at a cell and determine if
conditional formatting is in effect (and maybe which condition is TRUE in
the case of multiple conditions). We also need a collection of cells with
active conditional formatting so we can iterate through them if necessary.

Bill

John Walkenbach <jo...@j-walk.com> wrote in message

news:OlxWlk#x#GA....@cppssbbsa02.microsoft.com...

[Peter Beach]

John and Bill,

Although it is a little bit clunky, the following macro appears to report
whether conditional format 1 applies to A5:A10 in the sheet. Obviously
processing more operators (the example only does greater than!) and all
three conditional formats would be more work, but maybe it's a starting
point??

Sub Seekfmt()
Dim i%
Dim S As Worksheet
Set S = Worksheets("Sheet2")

For i% = 5 To 10
Select Case S.Cells(i%, 1).FormatConditions.Item(1).Operator
Case 5: ' greater than
If S.Cells(i%, 1).Value > Evaluate(S.Cells(i%,
1).FormatConditions.Item(1).Formula1) Then
Debug.Print i%
End If
End Select
Next i%
End Sub

Not exhaustively tested (scarcely "brisk walk in the park" tested) but maybe
it will help Bill out?

Regards,

Peter

[PhilCxn]

John, Bill, Peter, and anyone else

There's a BIG problem with checking if the condition has been met in the
conditional formatting if the condition is a relative reference. When you use
FormatConditions.Item(1).Formula1 to get the formula, it returns it as relative
to the active cell. I tried to create a UDF to count the cells with a certain
conditional background color and gave up when I ran into that stumbling block.
I did create a macro that seems to accomplish the task. I have only run
preleminary tests on it and it definitly needs fine tuning. I'm posting it
below and your welcome to toy with it. Note: there may be errors in the macro
because what I'm posting is code that I typed in from a print out because I
don't have my orignal file. I kept my xl97 practice files on my C drive at
work, and when I went on vacation the computer guys decided to fix a printing
problem I had by reformatting my hard drive. The important files are on the
network but I did have to recreate my menubar and Personal.xls. Anyway, here's
the macro.

Sub CdlCellColorCount()

Dim SearchArea As Range
Dim Cell As Range
Dim ToCheck As FormatCondition
Dim CountMe As Boolean
Dim FmTemp As String
Dim Cnums As Integer
Dim x As Integer
Dim CdlBgColor As Integer
Dim TotalMatch As Integer
Dim Fmla1 As Variant
Dim Fmla2 As Variant
Dim FmSwitch As Variant
Dim InCell As Variant

Set SearchArea = Range("E1:H6")
CdlBgColor = 5 'This is the index number of the color (red) to look for.

Application.ScreenUpdating = False
For Each Cell In SearchArea
Cell.Select 'Unfortunately this is necessary
Cnums = Cell.FormatConditions.Count
If Cnums Then
Set ToCheck = Nothing
For x = 1 To Cnums
If Cell.FormatConditions(x).Interior.ColorIndex = CdlBgColor
Then
Set ToCheck = Cell.FormatConditions(x)
Exit For
End If
Next x
If Not ToCheck Is Nothing Then
CountMe = False
FmTemp = ToCheck.Formula1
If Asc(FmTemp) = 61 Then
Fmla1 = Evaluate(FmTemp)
Else
Fmla1 = Val(FmTemp)
End If
If Not IsNumeric(Fmla1) Then
Fmla1 = UCase(Fmla1)
InCell = UCase(Cell)
Else
InCell = Cell
End If
If ToCheck.Type = xlExpression Then
CountMe = Fmla1
Else
If ToCheck.Operator <= 2 Then
FmTemp = ToCheck.Formula2
If Asc(FmTemp) = 61 Then
Fmla2 = Evaluate(FmTemp)
Else
Fmla2 = Val(FmTemp)
End If
If Not IsNumeric(Fmla2) Then
Fmla2 = UCase(Fmla2) Then
InCell = UCase(Cell)
End If
If Fmla1 > Fmla2 Then
FmSwitch = Fmla1
Fmla1 = Fmla2
Fmla2 = FmSwitch
End If
End If
CountMe = Choose(ToCheck.Operator, InCell >= Fmla1 And
InCell <= Fmla2, _
InCell < Fmla1 Or InCell > Fmla2, Cell = Fmla1, Cell <>
Fmla1, _
Cell > Fmla1, Cell < Fmla1, Cell >= Fmla1, Cell <=
Fmla1)
End If
If CountMe Then TotalMatch = TotalMatch + 1
End If
End If
Next Cell
Cells(1, 1) = TotalMatch
Application.ScreenUpdating = True
End Sub

Phil.

Peter Beach wrote:
=======================

[Victor Eldridge]

John Walkenbach wrote in message ...

>I think I'm going to start compiling a list flaws in the Excel object model.

I'd advise that you use Access to store such a list... <g>

Seriously though, this reminds me of something I saw in the Excel-L forum.
Several years ago, someone by the name of Jan Holmbäck compiled a "wish list"
and then asked the readers to vote for the changes/additions that they'd most
like to see in future versions of Excel. (see the links below)

Jan Holmbäck then forwarded the results to the Excel development team.
Whether or not Microsoft actually did anything about it, I cannot say for sure.
What I can say for sure is that some of the features that users were wishing for
back then, have since been successfully incorporated into Excel.

Perhaps now, with the Office 2000 launch out of the way, the Excel development
team are wondering what to do next. Maybe it's time for us to give them some
ideas to work on. It would take some time & effort, but could be very rewarding.

Any takers ?

Developer's Wish Poll: The List
http://peach.ease.lsoft.com/scripts/wa.exe?A2=ind9604&L=excel-l&P=R16438

Developer's Wish Poll: The Poll
http://peach.ease.lsoft.com/scripts/wa.exe?A2=ind9604&L=excel-l&P=R16942

Developer's Wish Poll: The Result
http://peach.ease.lsoft.com/scripts/wa.exe?A2=ind9605&L=excel-l&P=R648