change row colour dependent on cell

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

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

.

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?

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 ,

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

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: