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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,988
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,099
Messages
5,570,210
Members
412,309
Latest member
Benky
Top