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,941
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,082,106
Messages
5,363,180
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top