Use color table number column to set background color

[wdjohnson1976]

Hello everybody, I'd like to use a vba (or formula, whatever is the less resource intensive) to set fill color of a cell based on a column value. For example, if I have a column with number 1 then fill the adjacent column with the color table value of that number.

I've grown quite frustrated with poking around in the color table and finding that several different colors have the same numerical value. Maybe filling them based on number will be more straight forward!

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
What cells are your numbers in? Or are you simply looking to show all of the ColorIndex values & colours?

 

[wdjohnson1976]

Hello, thanks for the quick reply. The cells with the color table numbers may be in various locations based on what project I'm in I suppose. What I'd like it to do is kind of a vlookup where I can type in the number in one cell, move right one cell and enter a function that would look to the left and fill based on that number. Seems tricky as it's easy to get the color value out of a colored cell but more difficult to write a color to a cell based on varying values. Maybe deals with some conditional formatting? I'm not that good with VBA, and limited in advanced formulas so please forgive my ignorance!

 

[Zenwood]

Try this. The macro searches column 3 ("C") starting at row 2 and highlight the row based on the value in column 3.

Enter 1 in C2, 2 in C3 and 3 in C4 and run the macro to test.

Sub AlphaRowColor()


Dim Row_ As Integer
Dim Column_ As Integer
Dim ColumnStart_ As Integer
Dim ColumnEnd_ As Integer
Dim LastRow_ As Integer
Dim Loop_ As Integer


'Paul Pfau 2019


Row_ = 2     '      Row to start highlighting in
Column_ = 3     '     Column to test
ColumnStart_ = 4     '     Highlight from here...
ColumnEnd_ = 6     '     ...to here
LastRow_ = Cells(Rows.Count, Column_).End(xlUp).Row
'––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
For Loop_ = 1 To LastRow_
If Cells(Row_, Column_) = "" Then
Exit Sub
'––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
ElseIf Cells(Row_, Column_) = "1" Then: Range(Cells(Row_, ColumnStart_), Cells(Row_, ColumnEnd_)).Interior.Color = RGB(225, 200, 200)
ElseIf Cells(Row_, Column_) = "2" Then: Range(Cells(Row_, ColumnStart_), Cells(Row_, ColumnEnd_)).Interior.Color = RGB(200, 225, 200)
ElseIf Cells(Row_, Column_) = "3" Then: Range(Cells(Row_, ColumnStart_), Cells(Row_, ColumnEnd_)).Interior.Color = RGB(200, 200, 225)
'––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
End If
Row_ = Row_ + 1
Next Loop_
'––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
End Sub

 

[Fluff]

You cannot do what you are asking for with formulas or UDFs. It will need to be either conditional formatting with one rule for every colour you want to use, or a macro, but both of these need to know where to look.
On option would be to select the cells with the numbers and use

Sub wdjohnson()
   Dim Cl As Range
   For Each Cl In Selection
      Cl.Offset(, 1).Interior.ColorIndex = Cl.Value
   Next Cl
End Sub

The numbers must between 1 & 56 inclusive

 

[wdjohnson1976]

Thanks for this, it does work for coloring adjacent cells but does not use the ColorIndex to define the color. The below post from Fluff does work!

 

[wdjohnson1976]

Thanks Fluff! This is perfect and works perfectly for what I need.

 

[Fluff]

You're welcome & thanks for the feedback