Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
How do I retrieve data based on multiple search criteria? | Excel Worksheet Functions | |||
have Excel retrieve conditional operator ( < = ) from a cell | Excel Worksheet Functions | |||
Search and retrieve | Excel Discussion (Misc queries) | |||
Can I retrieve a value and its format using the minimum function? | Excel Worksheet Functions |