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

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 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(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

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(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 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(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
>

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 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(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 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(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
> >

--

Dave Peterson

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 here:
> 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(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 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(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
> > >
>
> --
>
> 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 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 here:
> > 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(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 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(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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Many thanks Dave.

"Dave Peterson" wrote:

> 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 here:
> > > 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(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 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(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
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>