Identifying Cell Formats

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
Code:
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.
 

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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? :unsure:

Im not sure if this here will help.
:biggrin:
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
CF are based around your inputed formula. Just use the formulas criteria for the CF to Filter ?
 

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,058
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top