vlookup based on cell color

**kolette** · 06-23-2009, 09:50 AM

I am creating a staffing request spreadsheet for my managers to use.

In the first worksheet Column A contains a list of employee names. When the managers dbl click the names of the employees they are recalling for work (or laying off), the cell will turn green.

From there, I would like to look up the corresponding address associated with the names in the cells that have been turned green. (names and address are located on second worksheet in same workbook)

I want to copy both the name and address onto a third worksheet. (from that third sheet, I am going to generate mail-merge form letters in Word). But I only want the third sheet to contain the names/addresses of the cells that are colored green on the first sheet.

Thanks for your help.
Kolette

**CRIMEDOG** · 06-23-2009, 11:39 AM

It is probably not as elegant as you like - I would probably add a column in front of the names, have the mgrs put a 1 if they are calling in the employee. Now you can have formula if a#>0,...

**ChemistB** · 06-23-2009, 02:23 PM

First, as Crimedog suggested, insert a blank column B
Then I would do this in two parts. First write a worksheet event code for doubleclick (paste this code into the worksheet vba window)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 1 Then Exit Sub
If Target.Interior.ColorIndex > 0 Then      'if the target already is colored
    Target.Interior.ColorIndex = -4142      'remove the color
    Target.Offset(0, 1) = ""                'clear the cell in col B
Else
    Target.Interior.ColorIndex = 14         'set the background color to green
    Target.Offset(0, 1) = 1                 'set the cell in B to 1
End If

End Sub

This code will color your cell (adjust the color index for a specific color (1 to 56 I believe) and will put a 1 into column B of that row.
Then in Column C and over, you can use VLOOKUP function inside an IF statement to look in Column B, if there's a 1, then do a vlookup, if not, keep blank. If you need help with VLOOKUP, let us know.