Solved

Identifing the Excel cell according to cell color & put a code in adjacent cell

Posted on 2010-09-02
5
393 Views
Last Modified: 2012-05-10
Dear Experts,
As shown in attached file, We have a requirement in excel where we want to Identify the some cell according to cell color & put a code in adjacent cell.

Please suggest how to map the requirement,

Regards,
Nilesh.
Color-Identification.xls
0
Comment
Question by:tnilesh2
[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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 33585499
You'll have to use a User Defined Function (UDF) to get the interior color of a cell.

Paste the below function into a regular module in the VBE.

Wayne
Public Function ColorCode(rng As Range)
    Application.Volatile
    Select Case rng.Cells(1).Interior.ColorIndex
        Case 35: ColorCode = 1
        Case 15: ColorCode = 2
        Case -4142: ColorCode = 3
        Case 36: ColorCode = 4
        Case Else: ColorCode = CVErr(xlErrNA)
    End Select
End Function

Open in new window

0
 

Author Comment

by:tnilesh2
ID: 33585679
Dear webtubbs,
Thanks for looking in to, your solution works fine just two more questions -
1. What are the "Case No." for other colors,
2. If I change the cell color after applying the function then code does not changes unless I save & re-open the file or press "F2" on respective cell & press enter. How to tackle this we want auto updatio of code as per the color.
 
Regards,
Nilesh.
 
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 125 total points
ID: 33585983
Nilesh,

1. That depends on your Colour Palette setup in Excel. You can check by colouring a cell and running this macro....

    Sub ShowColorIndex()
        MsgBox Selection.Interior.ColorIndex
    End Sub

2. That's one of the limitations I'm afraid, and there is no way to get around it. Changing a colour on a cell does not raise any events which would cause and dependant formulas to recalculate. By adding "Application.Volatile", we can help a bit, but that will only cause the function to recalculate when *any* value in a cell changes. But you do not have to close and reopen the workbook - a simple recalculation (F9 key) will do the trick.

Wayne
0
 

Author Comment

by:tnilesh2
ID: 33586505
Dear Wayne,
Thanks for the solution.
Nilesh.
0
 

Author Closing Comment

by:tnilesh2
ID: 33586509
Thanks
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

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

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

Join & Ask a Question