Conditional Format search and retrieve

B

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
 
D

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
 
B

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
 
B

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
 
D

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).

Bony said:
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
Click to expand...
 
B

Bony Pony

Many thanks Dave.

Dave Peterson said:
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).
Click to expand...
 
Top