MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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


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.

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:


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,


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


Worked like a charm....

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

Much appreciated.