Color Formatting based on a different cell value

BVEBRAD

New Member
Joined
Nov 22, 2005
Messages
8
I need my spreadsheet to change the color of rows in a chart based on the value in cells J3 through J202. The values returned in cells J3:J202 are 1,2,3,4,5,6,7 & 8. The rows I want the color formatting for are B3:G202.

Initially I was using Conditional Formatting which allowed up to 4 colors (including none or white as a color). I now need 8 different color possibilities. I realize this can be done using VBA but I am not familiar with VBA and cannot understand any of the examples I have found so far.

Can anyone offer any help in this matter?

Thanks!
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You'll need to copy the following and paste it into the Worksheet module of the worksheet that contains your data (need to go to the Visual Basic Editor Alt+F11 then right-click on the sheet name in the top left pane and select "view code"):

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lkRng As Range, colorRng As String, cell As Range, i As Integer

Set lkRng = Range("j3:j202")
colorRng = Range("b3:g202").Address

i = 1

For Each cell In lkRng
    Select Case cell.Value
        Case 1
            Range(colorRng).Rows(i).Interior.ColorIndex = 2
        Case 2
            Range(colorRng).Rows(i).Interior.ColorIndex = 3
        Case 3
            Range(colorRng).Rows(i).Interior.ColorIndex = 4
        Case 4
            Range(colorRng).Rows(i).Interior.ColorIndex = 5
        Case 5
            Range(colorRng).Rows(i).Interior.ColorIndex = 6
        Case 6
            Range(colorRng).Rows(i).Interior.ColorIndex = 7
        Case 7
            Range(colorRng).Rows(i).Interior.ColorIndex = 8
        Case 8
            Range(colorRng).Rows(i).Interior.ColorIndex = 9
        Case Else
            Range(colorRng).Rows(i).Interior.ColorIndex = 0
    End Select
    i = i + 1
Next cell
End Sub
You will also have to amend the colorindex values to whatever colors you want.

Give em a shout if you get stuck.

Best regards

Richard
 

BVEBRAD

New Member
Joined
Nov 22, 2005
Messages
8
Hi Richard,

That works like a charm. Thank you very much. What a great site this is.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,937
FWIW, if your assigned color indexes increase by one according to the cell value, you could use:

Code:
For Each cell In lkRng
    Range(colorRng).Rows(i).Interior.ColorIndex = ((cell.Value + 1) Mod 10)
    i = i + 1
Next cell
instead of

Code:
For Each cell In lkRng 
    Select Case cell.Value 
        Case 1 
            Range(colorRng).Rows(i).Interior.ColorIndex = 2 
        Case 2 
            Range(colorRng).Rows(i).Interior.ColorIndex = 3 
        Case 3 
            Range(colorRng).Rows(i).Interior.ColorIndex = 4 
        Case 4 
            Range(colorRng).Rows(i).Interior.ColorIndex = 5 
        Case 5 
            Range(colorRng).Rows(i).Interior.ColorIndex = 6 
        Case 6 
            Range(colorRng).Rows(i).Interior.ColorIndex = 7 
        Case 7 
            Range(colorRng).Rows(i).Interior.ColorIndex = 8 
        Case 8 
            Range(colorRng).Rows(i).Interior.ColorIndex = 9 
        Case Else 
            Range(colorRng).Rows(i).Interior.ColorIndex = 0 
    End Select 
    i = i + 1 
Next cell
 

BVEBRAD

New Member
Joined
Nov 22, 2005
Messages
8
That is not the case in this application but I have saved the code for if and when it would be needed.

Thanks again.
 

Forum statistics

Threads
1,077,864
Messages
5,336,848
Members
399,106
Latest member
anoufal

Some videos you may like

This Week's Hot Topics

Top