Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel -- need formula based on cell color

Posted on 2013-11-02
Medium Priority
751 Views
Last Modified: 2013-11-02
Experts:

I'm using  a module that scans for certain patterns across thousand of rows.

If a pattern is found, the macro applies a cell background color = yellow to each record (columns A:X) .

For another activity, I now would like to use a formula to identify those records.  For example:

=If(A1=Yellow,"Error","Ok")   How can this be accomplished?

Thanks,
EEH
0
Question by:ExpExchHelp
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2
5 Comments

Author Comment

ID: 39618920
Not sure if those posts address what I'm looking for.

Probably my fault for not further elaborating.

If a cell (A1) color is yellow, I'd like to display in AB1 either "Yellow" or "X" or anything that can be used by another formula to differentiate between white and yellow cells.

Thanks,
EEH
0

LVL 22

Accepted Solution

CompProbSolv earned 2000 total points
ID: 39618930
Those links refer to using VBA and not a cell formula.  This one will show you how to create such a function:
http://en.kioskea.net/faq/6606-excel-formula-based-on-the-color-of-cell

The fourth post here may be helpful:
http://www.ozgrid.com/forum/showthread.php?t=82173

I think that creating the function is a better solution.
0

Author Closing Comment

ID: 39618936
Thanks... that did the trick.  ;)
0

LVL 22

Expert Comment

ID: 39618963
I played with this a bit and came up with the following function:

Public Function dispColorIndex(targetCell As Range) As Variant

dispColorIndex = targetCell.Interior.colorIndex

End Function

For your situation, put the following in B1:
=dispColorIndex(A1)

Do note that the formula does not automatically update.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month4 days, 15 hours left to enroll

#### 670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.