Conditional Format search and retrieve

[Bony Pony]

Hi everyone,
Does anyone know of a search function that wil "find all" cells that contain
conditional formats?

I recorded a macro that came up with this useful code

ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select

This selects them all as you would expect.

I want to step through each cell that has Conditional Formatting and pick up
the formula beneath it that performs the format. Is this possible?

Much obliged for any help!
Bony

 

[Dave Peterson]

If your recorded code worked ok...

Dim myRng as range
dim myCell as range

set myrng = nothing
on error resume next
set myrng = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
on error goto 0

if myrng is nothing then
'no cells found
exit sub
end if

for each mycell in myrng.cells
msgbox mycell.address 'or inspect the CF formula
next mycell

 

[Bony Pony]

Hi Dave,
Many thanks for this. If I use the lines:

Debug.Print myCell.Address
Debug.Print myCell.Formula

I get the address of the cell and the "visible" formula not the CF formula.

If I do a watch on mycell, I can see no value against any property of mycell
that holds the cf formula. I wonder where it's kept?

I tried variants of:
Debug.Print myCell.SpecialCells(xlCellTypeAllFormatConditions)

etc but predictably came up with type mismatch or argument not optional
errors. Any other ideas?

Best regards!
Bony

 

[Dave Peterson]

I'm not sure what you're doing, but when I can't remember what the syntax is,
I'll record a macro in a test workbook when I use that feature.

But there are other resources available, too.

Chip Pearson may have what you're looking for here:
http://www.cpearson.com/Excel/CFColors.htm

 

[Bony Pony]

HI Dave,
What I was trying to achieve was this:

In cell C1 I have a formula =A1+b1

C1 ALSO has a conditional format formula e.g.
=(round(a1,2)=round(b1,2))
This will return TRUE if a1 = b1 to 2 dec places.

So, I want a macro that will detect the Conditional Formatted cells and
return the =(Round formula. Currently I can detect the CF cells but it
returns the actual cell formula not the CF.

Anyway, can't even remember why I needed to now ...

Regards,
Bony

 

[Dave Peterson]

If you look at Chip's code, you'll see that he examines the .formula1 property
with lines like:

If CDbl(Rng.Value) >= CDbl(FC.Formula1)

You can do the same thing (if you still need to).

HI Dave,
What I was trying to achieve was this:

In cell C1 I have a formula =A1+b1

C1 ALSO has a conditional format formula e.g.
=(round(a1,2)=round(b1,2))
This will return TRUE if a1 = b1 to 2 dec places.

So, I want a macro that will detect the Conditional Formatted cells and
return the =(Round formula. Currently I can detect the CF cells but it
returns the actual cell formula not the CF.

Anyway, can't even remember why I needed to now ...

Regards,
Bony

 

[Bony Pony]

Many thanks Dave.

If you look at Chip's code, you'll see that he examines the .formula1 property
with lines like:

If CDbl(Rng.Value) >= CDbl(FC.Formula1)

You can do the same thing (if you still need to).