Discerning Cell Formatting

[excel328]

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?

[DonkeyOte]

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.

[sweep]

Hi,

Something along these lines should do what you ask.

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

Please Login or Register  to view this content.

[martindwilson]

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

[excel328]

sweep's method works for me, but it is pretty computationally intensive on large ranges.

DonkeyOte's method works well with one condition, but the problem with this is that it seems like the additional conditional formatting rules don't update properly after I have use more than one condition. When I have two conditions with conditional formating that I applied on the same range, cells formatting changes within the range does not cause proper color updates when the conditional formatted range is extended. Conditional formatting is also not performing the right type of formatting withing the original range.

I have set currency formatting to green and text formatting to blue.
I have currency formatting in a2 for cf condition and this is set for the green , when I change the cell format of a2 from currency to accounting the cf treats the currency and accounting as the same. Also it seems like fraction, time, long date, and general formatting is treated like text formatting for the text conditional formatting condition.

[DonkeyOte]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

[excel328]

Thanks for the help. I will use all of these method in situations that seems fit.