Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-02
5
Medium Priority
?
404 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 48

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 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

926 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