Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

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

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


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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.