How can I logically test the format of another cell in excel

Question

I know about conditional formatting. I want to do the test in the opposite direction.

To simplify, I want to essentially do this:

if ((Text_Align(A1)='left',"L","R")  

or

if ((Background_Color(A1)="Pink", "Red dominates the background", "Just another blah background")

if ((Fontweight(A1)="Bold", "That was a strong statement", "A cell filled by a bean counter")

So far I've not found a function that can do the equivalent of Text_Align -- that is, test what the value of a cell's format.

Is this possible?

Answer 1

You can get some information about the cell like format etc using the CELL function e.g. :

=CELL("format", H2)

This supports a number of other arguments instead of format defined here.

However, if the options for this function do not return what you need then you may need to use VBA as proposed in Bathsheba's answer.

Answer 2

You can do this in VBA. Here's some prototype code:

Public Function test(ByVal rng As Range)
    Application.Volatile
    Select Case rng.HorizontalAlignment
    Case xlLeft
        test = "L"
    Case xlRight
        test = "R"
    Case Else
        test = "?"
    End Select

End Function

Note the line Application.Volatile. This sets the function as volatile. It tells VBA that the function return value is not purely a function of the input cell value: formatting changes do not trigger recalculation. Having changed some formatting, use F9 to recalculate the workbook.