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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
That is not the case in this application but I have saved the code for if and when it would be needed.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,214,769
Messages
6,121,434
Members
449,032
Latest member
egspen2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top