Selecting a cell based on format

[PatrickW]

Hi all

We know that conditional formatting takes the value of a cell, and thereby determines its format.

I'm looking to go the other way, or at least partly. I'm wanting to use the format of a cell in a formula.

As an example, I have a column of data: some of which are bold+italicised, and others of which are normal text, however, the text formatting implies something specific about those particular entries.

I would like to be able to return a value of TRUE or FALSE, depending on whether the cell text is bold (or bold+italics, or italics), or just plain text.

In looking for a solution to this problem I've done some forum searching here and elsewhere, and looked at the built-in help - and I've come across the various arguments of the cell("info type",reference) formula.

In this, I am able to isolate a few properties of the cell (including text alignment, number format and others) but not whether the text is bold/italicised or similar. As a workaround, I've manually right-aligned the text that is bold+italicised, and I can then grab those in a formula: if(cell("prefix",A1)=",something,something_else) - but is there a way to do this without the 'alignment' workaround?

As an additional question, is there a formula argument/formula-based solution that returns cell fill colour? I'd like to try and address this without having to play in VBA, if possible.

Thanks for your assistance

PatrickW

(Excel 2007)

 

[jasonb75]

Do those cells already have conditional formatting? If so use the CF criteria in the formula.

If not, then as far as I know, there is only 1 other way without VBA, see http://www.mrexcel.com/forum/showthread.php?t=20611

 

[PatrickW]

Well blow me down! There you go!

Regarding your first question, no - there is no conditional formatting in the cells, otherwise I'd've grabbed onto whatever the CF was using - but I've never even heard of the get.cell function.

It'll allow me to return any cell property I can think of!

I wonder why this isn't available as a standard formula now?

Thanks for your prompt, concise and correct answer, Jason. I really appreciate it!

PatrickW

 

[jasonb75]

Would be so much easier if it was avaiable as a standard formula, think you should be able to do pretty much anything you need to with it in named range though.

I'm not sure if the copy / paste to another sheet / crash issue mentioned in reply #3 of that thread is applicable to 2007 but would avoid that practice to be safe.