Discerning Cell Formatting

Is there a quick way to tell which cells are formatted to which type when you have formatted many cells with different formattings? I know that in the home tab will give you the type of formatting in the number portion of the tab. Is it possible to color each cell that is formatted to different types such as text cells being blue and currency cells being green?

Is this a one-off exercise for spot checking or something more permanent ?

You might consider using Styles or Conditional Format tests (see CELL function)

For ex. if you applied the format of interest to say Cell A1 you could have a format rule on the other cells validating against that format.

For sake of demo. assume B1:Z1 hold various values in various formats.

You highlight B1:Z1 and apply a rule of:

=CELL("format",B1)=CELL("format",$A$1)
apply colour as desired

You format A1 to say Currency and those Currency cells in B1:Z1 will be highlighted.

Some fairly major notes however:

-- using the CELL function route "Text" and "General" will appear to XL to be the same [G] - unclear if a major issue or not ?

-- Decimal significance... if irrelevant use only the left char of the format code (ie C2 and C1 are the same - compare C only)

-- Locale alterations ... CELL will return different results for Currency formatting pending client locale so this can preclude it's usage depending on scope.

-- Altering cell formats is a non-volatile action (ie won't trigger a recalculation)

VBA wise - if that's your preference, I'm not sure if there's a quick way to identify all cells simultaneously given you're concerned not with the data type of the underlying value(s) but with the explicit format applied to the cells themselves.
I'm probably wrong though.

Hi,

Something along these lines should do what you ask.

Select the cells you require tested, and run this code.

Sub ColourBasedOnFormat()

Dim rCell As Range
For Each rCell In Selection
If rCell.NumberFormat = "$#,##0.00" Then rCell.Interior.ColorIndex = 5
If rCell.NumberFormat = "@" Then rCell.Interior.ColorIndex = 4
Next
End Sub

you could use more func add in i think
=XLM.GET.CELL(7,A1,TRUE)
returns
General or @ or 0.00 Or_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "-"??_-;_-@_-
not sure of the practicalities to use it in CF you need to make it a named formula tho

As outlined there are some fairly major limitations using the standard CELL function and as mentioned the VBA route would warrant a cell by cell iteration given the format rather than value takes precedence (at least AFAIK).

Martin raises the point that the XLM GET.CELL call will return more info. but this is still in effect VBA though you can utilise via Names rather than via VBE (or via 3rd party add-in as advised).
(note: XL2007 onwards use of XLM calls will necessitate file be saved as .xlsm)

For ex. - using your sample file - with B1 the active cell you can create the following Defined Names:

Name: _meformat
RefersTo: =GET.CELL(7,B1)

Name: _a1format
RefersTo: =GET.CELL(7,$A$1)

Name: _a2format
RefersTo: =GET.CELL(7,$A$2)

With B1:G6 highlighted you can amend your rules to:

=_meformat=_a1format

=_meformat=_a2format

That said there are still some fairly major limitations to this approach - most notable of which is the fact that altering the explicit format of a cell is a non-volatile action.
Until such time as a volatile action is performed (thereby invoking a calculation) it follows that the Conditional Formatting rules will not re-evaluate.