Color Formatting based on a different cell value

[BVEBRAD]

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]

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"):

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]

Hi Richard,

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

 

[Oaktree]

FWIW, if your assigned color indexes increase by one according to the cell value, you could use:

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

instead of

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]

That is not the case in this application but I have saved the code for if and when it would be needed.

Thanks again.