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: