Change color of multiple cells depending on cell value

[stevelucky]

I know how to do what I'm trying to accomplish using Conditional Formatting, but it's WAY too tedious.

Here's the scenario. I have about 60 rows of cells. I want the color of the text in each row to be determined by the value in the first cell of that row. For example, if the value in A1 is "Jon" then the font color in A1-A3 will be blue. But if the value in A1 is "Cindy" then the font color in A1-A3 will be pink. I want the same conditions to apply to all rows, or at least rows 1-60. There will be about 8 different values, thus 8 different colors. Doing this with Conditional Formatting will take FOREVER. I'll have to set the formatting for each value, for each row. 8 values X 60 rows = 480. That's too many time to have to format. Any other ideas?

 

[ChrisUK]

You could try using the cell change event like this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" Then
Select Case Target.Value
Case 1: For x = 1 To 5
Cells(2, 3 + x).Interior.ColorIndex = 6
Next
Case 2: Cells(2, 2).Interior.ColorIndex = 16
Case 3: Cells(2, 2).Interior.ColorIndex = 20
Case 4: Cells(2, 2).Interior.ColorIndex = 32
Case Else: Cells(2, 2).Interior.ColorIndex = 45
End Select
End If

End Sub

If you now change the contrents of cell A1 you will see you get a different colour displayed in cell B2 or I've put a loop in as well to cover a rnage of cells if the value is 1

Hope this helps

Chris

 

[stevelucky]

Where do i specify what the value is? There are going to be 8 different names of people in the office (and 8 different colors).

 

[ChrisUK]

after the case statement ....

Case "Jon": For x = 1 To 5
Cells(2, 3 + x).Interior.ColorIndex = 6
Next
Case "Fred": Cells(2, 2).Interior.ColorIndex = 16
Case "Bill": Cells(2, 2).Interior.ColorIndex = 20
Case "Jim": Cells(2, 2).Interior.ColorIndex = 32
Case Else: Cells(2, 2).Interior.ColorIndex = 45

You might think about using the ucase function or if you enter jim and the case statement says Jim it won't work! Shift it all to upper or lower case

Chris

 

[stevelucky]

This is making sense. Forgive my ignorance, but what does the "x= 1 to 5" do?

Also, what does "Cells(2, 3 +x)" do?

And what does "Cells(2, 2)" do?

I'm trying to figure out what I need to tweak to make this work for my exact scenario.

This is what I've got right now:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" Then
Select Case Target.Value
Case "Theresa": For x = 1 To 5
Cells(2, 3 + x).Interior.ColorIndex = 6
Next
Case "Henry": Cells(2, 2).Interior.ColorIndex = 16
Case "Greg": Cells(2, 2).Interior.ColorIndex = 20
Case "Ron": Cells(2, 2).Interior.ColorIndex = 32
Case Else: Cells(2, 2).Interior.ColorIndex = 45
End Select
End If

End Sub



And it doesn't seem to be working. The cells I'm trying to effect are in columns A, B, and C for rows 1-60. It can actually be all rows, as long as it's only columns A, B, and C.

 

[ChrisUK]

Steve.

OK, lets take this bit by bit ...

For x = 1 to 5
...
...
Next

These lines are a loop, the loop starts with a 1 and finishes with a 5 but goes through each value inbetween - 1,2,3,4,5

It's a very useful way of doing something a set number of times without repetatively writing it out . So, the ... would be commands which are executed for each value in the loop - 1,2,3,4,5

Next ...

Cells(2, 3 +x)

Cells is a built in function and takes two arguments, row and column. It's a way to reference a specific cell in the CURRENT workbook. So, cells(2,2) is referencing B2, cells(1,1) would reference A1 and Cells(3,3) would reference C3.

If you put this into your loop:

Cells(1,2+x) would reference A(2+x) and if x will equal 1,2,3,4,5 this would be A2,A3,A4,A5,A6,A7

Now then, you say it's not working, this could be down to several factors but I think there are two places we can look at for a start.

The whole piece of code MUST be in the "ThisWorkbook" object and NOT in a worksheet object

Second, this code only looks at the value of the cell A1:

If Target.Address = "$A$1" Then

This line is the one which we need to change if you want to look at several cells. I would look at constructing another loop for this, have a go and post back if you get stuck.

TIP: If you're unsure of function or what it does, click on the work (ie cells) and press F1. Excel will open up the help window with this function highlighted and usually quite a good explanation along with some examples

Hope this helps you.

Chris

 

[stevelucky]

it's still not working for me. i've put everything in ThisWorkbook and my code is as follows:

Private Sub Workbook_SelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" Then
Select Case Target.Value
Case Theresa: For x = 1 To 3
Cells(1, 2 + x).Interior.ColorIndex = 6
Next
Case Henry: Cells(1, 2).Interior.ColorIndex = 16
Case Greg: Cells(1, 2).Interior.ColorIndex = 20
Case Ron: Cells(1, 2).Interior.ColorIndex = 32
Case Else: Cells(1, 2).Interior.ColorIndex = 45
End Select
End If

End Sub

are the names (henry, ron etc...) supposed to be in quotes? i don't think so. but it's still not working. this should just be for A1 as my target cell to change and it will change the colors of A1, A2, and A3 correct? that's what i'm aiming for. any ideas?