Change interior color based on cell value of color constant

1
votes

I have an Excel range with the 8 color constants. I just wanted to simply change the interior color of the cell in the adjacent column based on the cell value. Unfortunately, I get a type mismatch which is because it is treating the value as a string, but I don't know how to convert it to constant.

For example, to change interior color to blue this works rng.interior.color = vbblue

but not this rng.interior.color = rng.value 'because it is a string "vbblue"

What can I do to convert rng.value from string to the color constant so the range in column E gets background color based on value in column D?

range

Dim colorRange As Range
Dim rng As Range
Set colorRange = Range("D1", Range("D1").End(xlDown).Address)

For Each rng In colorRange
  rng.Offset(, 1).Interior.Color = rng.Value
Next rng
2
excelvba

2 Answers

2
votes

There is no "built-in" way to convert the name of a Constant to its value. You need to do the mapping yourself. Best is to use a dictionary:

Function TextToColor(s As String) As Long
  Static dict As Dictionary
  If dict Is Nothing Then
    Set dict = CreateObject("Scripting.Dictionary")
    dict("vbBlack") = vbBlack
    dict("vbWhite") = vbWhite
    dict("vbRed") = vbRed
    dict("vbGreen") = vbGreen
    dict("vbBlue") = vbBlue
    dict("vbYellow") = vbYellow
    dict("vbMagenta") = vbMagenta
    dict("vbCyan") = vbCyan
    ' dict("etc...") = etc...
  End If
  TextToColor = dict(s)
End Function

Sub ExampleUse()
  Dim colorRange As Range: Set colorRange = Range("D1", Range("D1").End(xlDown).Address)
  Dim rng As Range
  For Each rng In colorRange
    rng.Offset(, 1).Interior.Color = TextToColor(rng.value)
  Next rng
End Sub
0
votes

You can do it directly with ColorIndex instead. So if the cell says 5 or the related color index number, there's no need to change the code other than this line:

rng.Offset(, 1).Interior.ColorIndex = rng.Value