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 again, Chip Pearsons code works fine for me - did you try it?

However, there isnt an example for other formatting but I suspect the same technique could be used to ascertain these values. The code is reasonably complex so would take some time (for me anyway) to step through it and understand it properly in order to create a UDF that works for bordering and font options.

Ivan makes a good point about using the same formulas in the cf to determine whether formatting would apply to the range. If you used the same formulas and the result was True then you know formatting would apply. Therefore, its just a matter of looking at the cf to determine what formatting is there then including this in your formulas.

It wouldnt be foolproof, as changes to the cf (either change of conditions or formatting) would mean you would need to amend the formula.

 

[redart]

No I must admit I didn't try Chip's code as my VBA skills are next to zero and as you say it was pretty complex. As for Ivan's suggestion of using the cf formulas, I referred to this earlier in the post as being something I'd looked at but found to be too cumbersome for the same reasons you highlight. The GET.CELL and Insert|Name|Define option would easily be the most efficient way of doing this, but the more I look at it the less likely it seems it can be made to work with cf's.
May have to settle for the belts and braces approach on this one. Thanks for all the suggestions anyway.

Tony

 

[parry]

I hear you but unfortunately your going to have to do a bit of mucking around if you want to capture cf formatting.

Chips code will be the business for colours so you could just use that to determine what other formatting was used if the colour and font and borders were all part of the same condition.

For example, say you only had one cf condition and this applied the Colour Red, Bold font and a border around the cell if the condition were true. Therefore, as all formatting is applied under the one condition, you will know that if the cell is red then it must also be bold and with a border.

You can use the CountOfCF function to determine if there is cf on the cell then just check for the colour using the ColorIndexOfCF function. This assumes that a colour is part of the formatting.

You dont need to understand Chips code in order to use its functionality. Follow these steps.

1. Open the VBE (Alt+F11)
2. Select Insert|Module from the menu
3. Copy the code from the line Function ActiveCondition(Rng As Range) As Integer right to the very end of the page up to & including the line End Function.
4. Paste the code in the right hand window (big white area).
5. Close the VBE (Alt+Q)

To use see the instructions on Chips page. Its just a collection of custom formulas so you enter them like any other formula.

eg to find the conditional format colour for cell A1 use this...
=ColorIndexOfCF(A1,FALSE)

It will return a number. The # -4142 being a balnk cell (no interior colour format) and the number 3 red for example.