Conditional Format search and retrieve
 

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(xlCellTypeAllFormatConditi ons).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

Conditional Format search and retrieve
 

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(xlCellTypeAllFormatConditi ons)
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 wrote:

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(xlCellTypeAllFormatConditi ons).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

Conditional Format search and retrieve
 

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" wrote:

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(xlCellTypeAllFormatConditi ons)
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 wrote:

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(xlCellTypeAllFormatConditi ons).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

Conditional Format search and retrieve
 

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 he
http://www.cpearson.com/Excel/CFColors.htm



Bony Pony wrote:

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" wrote:

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(xlCellTypeAllFormatConditi ons)
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 wrote:

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(xlCellTypeAllFormatConditi ons).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


--

Dave Peterson

Conditional Format search and retrieve
 

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" wrote:

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 he
http://www.cpearson.com/Excel/CFColors.htm



Bony Pony wrote:

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" wrote:

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(xlCellTypeAllFormatConditi ons)
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 wrote:

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(xlCellTypeAllFormatConditi ons).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


--

Dave Peterson

Conditional Format search and retrieve
 

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 Pony wrote:

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" wrote:

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 he
http://www.cpearson.com/Excel/CFColors.htm



Bony Pony wrote:

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" wrote:

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(xlCellTypeAllFormatConditi ons)
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 wrote:

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(xlCellTypeAllFormatConditi ons).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


--

Dave Peterson


--

Dave Peterson