Can tou write a formula based on a cells format ??

Posted by Brian Krisanski on January 16, 2002 3:56 PM

hi,

It is straight forward to format a cell based on a formula in conditional formatting, however what I am trying to do is sort of the reverse....

I want to write a conditional formula based on the format (i.e. colour) of particular cells.

Is this possible ?? If so, I would very much like to know how to achieve it.

Thanks
Brian Krisanski

Posted by Russell Hauf on January 16, 2002 4:33 PM

Well, the answer is yes, but I believe you will need a user defined function. Here is an example:



Public Function GetColorIndex(cl As Range) As Integer 
    GetColorIndex = cl.Interior.ColorIndex 
End Function

Ok, now in your workbook you can use this formula. So say Cell A1 has a light yellow background. In cell B1, if you type the following:

=GETCOLORINDEX(A1)

It should return 36. So you could base your formula on the number this returns, or just use it in your formula something like this:

IF(GETCOLORINDEX(A1) > 30, A1+A2, "")

Hope this makes sense & helps,

Russell

Posted by Brian Krisanski on January 16, 2002 4:57 PM

Excellent....

Worked like a charm....

Thankyou very much.... You just saved me literally hours of work....

Much appreciated.
Brian