Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

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

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
tnilesh2
Asked:
tnilesh2
  • 3
  • 2
1 Solution
 
Wayne Taylor (webtubbs)Commented:
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
 
tnilesh2Author Commented:
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
 
Wayne Taylor (webtubbs)Commented:
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
 
tnilesh2Author Commented:
Dear Wayne,
Thanks for the solution.
Nilesh.
0
 
tnilesh2Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now