Solved

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

Posted on 2010-09-02
5
349 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
  • 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Outlook Free & Paid Tools
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now