| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour rows
I have never used VBA so this might be very basic. I have an excel
spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
Rightclick on sheet-tab and select show programcode
insert code below in window to the right Code test in column B change if u like Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub If Target = "FU" Then Target.Interior.ColorIndex = 3 If Target = "FR" Then Target.Interior.ColorIndex = 4 If Target = "SU" Then Target.Interior.ColorIndex = 5 If Target = "PE" Then Target.Interior.ColorIndex = 6 If Target = "??" Then Target.Interior.ColorIndex = 7 If Target = "??" Then Target.Interior.ColorIndex = 8 If Target = "??" Then Target.Interior.ColorIndex = 9 If Target = "??" Then Target.Interior.ColorIndex = 10 End Sub "John D" skrev: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
I have tried the following code which was in "VBA - Coloring Rows Instead of
Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
this one reset color
If Target = "" Then Target.EntireRow.Interior.ColorIndex = xlNone "excelent" skrev: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
Thanks for the advice it sort of works that is it has colored the lines but
doesn't readily change the color. When debugging it comes up with run time error '13' type mismatch and it highlights If Target = "FU" Then Sorry I don't understand "excelent" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
I have worked out what was happenning and it works beautifully now thanks
very much. However now I would like to limit it to a range of columns that is from B to M any ideas how to do this? "John D" wrote: Thanks for the advice it sort of works that is it has colored the lines but doesn't readily change the color. When debugging it comes up with run time error '13' type mismatch and it highlights If Target = "FU" Then Sorry I don't understand "excelent" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
I have tried the following to get it to limit the coloring to B to M
however it is deleting the color from those cells and leaving the rest colored in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) For myloop = 2 To 13 ActiveSheet.Cells(Target.Row, myloop).Interior.ColorIndex = icolor Next If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.myloop.Interior.ColorIndex = 3 If Target = "FR" Then Target.myloop.Interior.ColorIndex = 4 If Target = "SU" Then Target.myloop.Interior.ColorIndex = 5 If Target = "PE" Then Target.myloop.Interior.ColorIndex = 6 If Target = "NS" Then Target.myloop.Interior.ColorIndex = 7 If Target = "CL" Then Target.myloop.Interior.ColorIndex = 8 If Target = "HO" Then Target.myloop.Interior.ColorIndex = 9 If Target = "" Then Target.myloop.Interior.ColorIndex = xlNone End Sub "John D" wrote: I have worked out what was happenning and it works beautifully now thanks very much. However now I would like to limit it to a range of columns that is from B to M any ideas how to do this? "John D" wrote: Thanks for the advice it sort of works that is it has colored the lines but doesn't readily change the color. When debugging it comes up with run time error '13' type mismatch and it highlights If Target = "FU" Then Sorry I don't understand "excelent" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
Do u mean color from B to M or trick in B to M ? (this one trick)
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B12:M2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 If Target = "" Then Target.EntireRow.Interior.ColorIndex = xlNone End Sub "John D" skrev: I have worked out what was happenning and it works beautifully now thanks very much. However now I would like to limit it to a range of columns that is from B to M any ideas how to do this? "John D" wrote: Thanks for the advice it sort of works that is it has colored the lines but doesn't readily change the color. When debugging it comes up with run time error '13' type mismatch and it highlights If Target = "FU" Then Sorry I don't understand "excelent" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub If Target = "FU" Then Target.EntireRow.Interior.ColorIndex = 4 If Target = "FR" Then Target.EntireRow.Interior.ColorIndex = 46 If Target = "SU" Then Target.EntireRow.Interior.ColorIndex = 6 If Target = "PE" Then Target.EntireRow.Interior.ColorIndex = 3 If Target = "NS" Then Target.EntireRow.Interior.ColorIndex = 2 If Target = "CL" Then Target.EntireRow.Interior.ColorIndex = 9 End Sub "John D" skrev: I have tried the following code which was in "VBA - Coloring Rows Instead of Cells" question earlier however it doesn't work for me so maybe I have done something wrong. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G12:G2000")) Is Nothing Then Select Case Target Case "FU" icolor = 4 Case "FR" icolor = 46 Case "SU" icolor = 6 Case "PE" icolor = 3 Case "NS" icolor = 2 Case "CL" icolor = 9 Case Else 'Whatever End Select Target.EntireRow.Interior.ColorIndex = icolor End If End Sub "John D" wrote: I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
The free Excel add-in "Shade Data Rows" should do what you want. -shades by row value or by every nth row or by row group of n size. -shades entire row or just the selection -shades visible rows only or hidden and visible rows -choice of any color -does not use conditional formatting Download from... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA "John D" <John wrote in message I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Colour rows
Thank you Jim Cone and Excelent your suggestions are great and I shall try
out both. Excelent's because I want to learn and Jim's because it sounds like a simple and ongoing solution. Again thank you "Jim Cone" wrote: The free Excel add-in "Shade Data Rows" should do what you want. -shades by row value or by every nth row or by row group of n size. -shades entire row or just the selection -shades visible rows only or hidden and visible rows -choice of any color -does not use conditional formatting Download from... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA "John D" <John wrote in message I have never used VBA so this might be very basic. I have an excel spreadsheet and on it is a column that the user can place different initials to indicate status of client e.g. FU; FR; SU; PE and so on. I am wanting if its possible to have excel colour the row with a different colour for each variation. Can anyone suggest anything please? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use conditional formatting to colour rows of data? | Excel Worksheet Functions | |||
| change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
| Formula to change rows a different colour? | Excel Worksheet Functions | |||
| make colour in rows stay | Excel Discussion (Misc queries) | |||
| Text in Blue colour, but print in black colour | Excel Worksheet Functions | |||
Hybrid Mode
