# VLOOKUP by cell background color

In this article you will learn how we can use VLOOKUP by a cell background color. We will create a User Defined Function (UDF) in VBA and will access it on the worksheet. We can use this function when we have to get the value from a table on the base of cell background color is place of lookup value.

• Copy the below given code and go to Visual Basic Editor (Press Alt+F11)
• Insert a module (Press Alt+I+M)
• Paste this code in the module.
• Save as the workbook as Macro enable workbook.
```Function myvlookup(r1 As Range, r2 As Range, n As Integer, a As Boolean) As Variant

Application.Volatile

Dim cel As Range
Dim i As Integer
i = 0
For Each cel In r2
If cel.Interior.Color = r1.Interior.Color Then
myvlookup = cel.Offset(0, n - 1).Value
i = i + 1
If a = False Then
Exit For
End If
End If

Next

If i = 0 Then myvlookup = "#N/A"

End Function```

#### See the below example:

Let’s say we have below given data on Range “A1:B15” and we have to get the sales from column B on Range “E2:E4” on the base of Range “D2:D4” cell background color.

Since we have pasted the above given code in the module so “myvlookup” function will be available in this workbook now.

Put the formula =myvlookup(D2,\$A\$1:\$B\$15,2,0) on Range “E2”.

Fill down the formula till E5.

Since we have used 0 (Zero) in our formula “(=myvlookup(D2,\$A\$1:\$B\$15,2,0)” so it will give value which will be found first.

As you are seeing in below image value available on column E are the first found values.

To get the value which is available in the last we need to use 1 in place of 0 in myvlookup formula “(=myvlookup(D2,\$A\$1:\$B\$15,2,1)”