Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 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

14 Experts available now in Live!

Get 1:1 Help Now