Formatting VLOOKUP Results

**Hulk** · 05-16-2005, 10:52 PM

Is there a way to format the results of a VLOOKUP.

For example, If the results of a VLOOKUP come back as the word "black", I would like all 5 letters of the word to be black. If the VLOOKUP returns the word "purple", I would like all 6 letters to be the color purple.

Is this possible without programming since I am pretty new to this?

If programming is the only way to solve the problem, and is relatively easy for a beginner, please provide me with code for the example above and I will try to make sense out of it for my purposes.

Thank you!

**duane** · 05-16-2005, 11:15 PM

Here is a start - how many possible colors are there?

Sub Macro1()
' this formats cell a7 based on cell content
If Cells(7, 1) = "red" Then Cells(7, 1).Font.ColorIndex = 3
If Cells(7, 1) = "blue" Then Cells(7, 1).Font.ColorIndex = 5
If Cells(7, 1) = "black" Then Cells(7, 1).Font.ColorIndex = 1
If Cells(7, 1) = "purple" Then Cells(7, 1).Font.ColorIndex = 13
End Sub

**Hulk** · 05-17-2005, 09:14 AM

How do you identify the number associated with a color? For example, I use a shade of grey that is customized.

Also, if the result comes back as "red black", is it possible to make the first three characters the color red and the last 5 characters black?

**swatsp0p** · 05-17-2005, 09:58 AM

Add this code to your module:

-------------------------

If Cells(7, 1) = "Red Black" Then Range("A7").Select
With ActiveCell.Characters(Start:=1, Length:=3).Font
.ColorIndex = 3
End With
With ActiveCell.Characters(Start:=5, Length:=5).Font
.ColorIndex = 1
End With

--------------------------

Note: the text is case sensitive, you may need to also check for "red black" as well as "RED BLACK" etc. (or use data validation in your cells).

Use this format to set tests for other combinations, e.g. (Blue Green), changing Start and Length parameters to meet your needs (don't forget to count the space between words!)

Here are the 40 standard colors and their VBA number: (you may have to test to get the number for a 'custom' color. It may require using the RBG code. I am not sure how to return that code).

BLACK 1
WHITE 2
RED 3
LT. GREEN 4
BLUE 5
YELLOW 6
PINK 7
TURQUOIS 8
DARK RED 9
GREEN 10
DARK BLUE 11
DARK YELLOW 12
VIOLET 13
TEAL 14
GRAY-25% 15
GRAY-50% 16
SKY BLUE 33
LT. TURQUOIS 34
LT. YELLOW 36
PALE BLUE 37
ROSE 38
LAVENDER 39
TAN 40
LT. BLUE 41
AQUA 42
LIME 43
GOLD 44
LT. ORANGE 45
ORANGE 46
BLUE-GRAY 47
GRAY-40% 48
DARK TEAL 49
BRIGHT GREEN 50
DARK GREEN 51
OLIVE GREEN 52
BROWN 53
PLUM 54
INDIGO 55
GRAY-80% 56

Good Luck

Bruce