MrExcel Publishing
Your One Stop for Excel Tips & Solutions

change row colour dependent on cell


Posted by Jacq on February 12, 2002 3:24 AM

Hi

I have a worksheet where I want to the change, on activate of the worksheet, the row colour depending on the cell in column G. which is one of 5 choices selected from a list box (VG, G, A, B, VB)
e.g.
cell g1 = "vg" so range a1:g1 colour is red
cell g2 = "vb" so range a2:g2 colour is yellow
cell g5 = "a" so range a5:g5 colour is blue

etc etc

has any kind person got code for this
any help much appreciated
Jacq


Posted by Tim on February 12, 2002 4:43 AM

not the best solution, but might work for you ... ?

I have been waiting for someone to reply to your post, as I want to do the same kind of thing.

I have got round my problem by sorting my data on the row i want to be the determining cell, then manually change the colour or the cells, then sort it back to how it was. (Using Data > Sort).

This worked for me ... I can't think of any other way ... let me know if you get a response, yeah?

Thanks
Tim

Posted by Tom Urtis on February 12, 2002 6:45 AM

See if this works for you.
Right click on your sheet tab, left click on View Code, and paste this in.
I took your post to mean that "vg", "vb", and "a" are entered in lower case in order for the cell shading to take effect.
-------------------------------------------


Private Sub Worksheet_Change(ByVal Target As Range)

If [G1] = "vg" Then
[A1:G1].Interior.ColorIndex = 3
Else
Range("A1:G1").Interior.ColorIndex = 0
End If

If [G2] = "vb" Then
[A2:G2].Interior.ColorIndex = 6
Else
Range("A2:G2").Interior.ColorIndex = 0
End If

If [G5] = "a" Then
[A5:G5].Interior.ColorIndex = 8
Else
Range("A5:G5").Interior.ColorIndex = 0
End If

End Sub


------------------------------------------
Any help?

Tom Urtis

.

Posted by Tim on February 12, 2002 8:05 AM

Hi,
I have been trying to do a similar thing ... and after reading your posting tried to manipulate your code so that the whole of a column will be looked at, not just an individual cell.
I thought using $ signs might work, but am unsure of where to put them.
eg instead of
:If [G1] = "vg" Then
:[A1:G1].Interior.ColorIndex = 3
I tried :
If [B:B} = ...etc
[A$:G$]... etc

but this did not work ... is this not going to work, or am i just doing it wrong?

Posted by Tom Urtis on February 12, 2002 8:23 AM

It sounds do-able. Please describe exactly what you are trying to do, and if the change depends on a single value, as in Jacq's case (and if so what that value is), or if any cell changes in any fashion in that column, or what. Also describe what you want to have happen, and what column you are talking about, and we can make this happen.

Tom Urtis ,

Posted by Hmm on February 12, 2002 10:10 AM

Ok. I was hoping to be able to look at what is in column A, check what it says in there, and colour the rest of the row accordingly. Thus the same as the script you gave earlier, but not dependent on specific cells, but on a whole column.

Something like:
If [*first* cell in column A] = "red" Then
[all the first row].Interior.ColorIndex = 3
Else
Range("all the first row").Interior.ColorIndex = 0
End If

If [*first* cell in column A] = "green" Then
[all the first row].Interior.ColorIndex = 6
Else
Range("all the first row").Interior.ColorIndex = 0
End If

If [*second* cell in column A] = "red" Then
[all the second row].Interior.ColorIndex = 3
Else
Range("all the second row").Interior.ColorIndex = 0
End If

If [second cell in column A] = "green" Then
[all the second row].Interior.ColorIndex = 6
Else
Range("all the second row").Interior.ColorIndex = 0
End If

Can this be done without actually typing in A1, A2, A3 etc .... can it be done using arrays or $ signs?

My rapidly failing programming knowledge is frustratiing me!
thanks

Posted by Tom Urtis on February 12, 2002 11:26 AM

See if this is what you need with these examples.
Again, right click on the sheet tab, left click on View Code, and paste this in.

T.U.
'''''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 1 Then Exit Sub

Select Case Target

Case "Red"
Target.EntireRow.Interior.ColorIndex = 3

Case "Yellow"
Target.EntireRow.Interior.ColorIndex = 6

Case "Blue"
Target.EntireRow.Interior.ColorIndex = 8

Case "Green"
Target.EntireRow.Interior.ColorIndex = 4

Case "Brown"
Target.EntireRow.Interior.ColorIndex = 9

Case "Orange"
Target.EntireRow.Interior.ColorIndex = 45

Case Else
Target.EntireRow.Interior.ColorIndex = 0

End Select

End Sub

'''''''''''''''''''''''''''''''''''''''''''' Ok. I was hoping to be able to look at what is in column A, check what it says in there, and colour the rest of the row accordingly. Thus the same as the script you gave earlier, but not dependent on specific cells, but on a whole column. Something like: