Identifying Cell Formats

[redart]

Hi All,

=CELL("FORMAT", REF) returns the number format type of a particular cell.
For example, =CELL("FORMAT",B9) returns the value "P0" if cell B9 is formatted to show percentage. Can anyone tell me how to return formats other than number formats, for instance colors, bold, underlining ?.

Thanks, Tony

 

[parry]

Hi, I dont think there is a standard Excel formula that provides this info. However a procedure or custom function could be created which does this.

eg

Public Function Bold(Target As Range) As Boolean
If Target.Font.Bold Then Bold = True
End Function

Note that changes in format do not cause Excel to recalculate so if you changed the font to bold then the function wouldnt update until some other change that does effect recalculation was made.

 

[PaddyD]

"Hi, I dont think there is a standard Excel formula that provides this info"

Depends what you mean by 'standard'


http://www.mrexcel.com/board2/viewtopic.php?t=21312

 

[parry]

"Hi, I dont think there is a standard Excel formula that provides this info"

Depends what you mean by 'standard'


http://www.mrexcel.com/board2/viewtopic.php?t=21312

True. Thanks, I wasnt aware of ye olde xl4macro that does the job.

 

[redart]

Thanks guys. The GET.CELL arguments list was what I was ultimately after, only I didn't know what it was called and therefore how to find it. Got a lot of formats I need to track down!.
Paddy - we've covered this before in another post, but is there no way to extend this technique to hunt down Conditional Formats?. Have searched the list and no mention of it. I know I can find these by using the cf formula itself, but with multiple cf's and there associated formulas, it would be neat to find them by just changing "type_num" in the defined name box, right?.

Thanks again, Tony

 

[parry]

I'll let Paddy advise about formula optons but you can identify which cells are part of a conditional format (not necessarily formatted as the cell may not meet the conditional test) by selecting Edit|Goto|Special|Conditional Formats.

The "All" option means select all cells that have conditional formatting in the sheet while the "Same" option means select all cells that have the same conditional format conditions as the active cell.

hth

 

[redart]

Parry,
The Edit|Goto route wont help me as I need to create filtered lists based on cf's, and then do analysis on the resulting subsets. I'm looking for a quicker way to filter based on cf's, without having to re-type /copy /
paste the numerous formulas every time.

Tony

 

[parry]

OK, its a bit unclear from your posts what your end objective is and its still a little muddy.

Im guessing your using the formulas to identify the formats so that you can then use the result as a criteria for the sort. In other words you have a column with the results (like PO for the percentage) and basing the sort of this column?

Im not sure if this here will help.

 

[Ivan F Moala]

CF are based around your inputed formula. Just use the formulas criteria for the CF to Filter ?

 

[redart]

Yes that's right Parry. I have a column with the reference to the format I'm looking for, and sorting on that. Perhaps it would clarify if I point you to this. Basically I want to do the same thing but with conditional formats.