Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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

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.

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.