Colour rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 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
Click to expand...
 
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:
 
this one reset color
If Target = "" Then Target.EntireRow.Interior.ColorIndex = xlNone


"excelent" skrev:
 
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
 
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?
 
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
 
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 (e-mail address removed)>
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?
 
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:
 
And this one trick in column G but color in B to M
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G12:G2000")) Is Nothing Then Exit Sub
If Target = "FU" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 4
If Target = "FR" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 46
If Target = "SU" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 6
If Target = "PE" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 3
If Target = "NS" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 2
If Target = "CL" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = 9
If Target = "" Then Range("B" & Target.Row & ":M" &
Target.Row).Interior.ColorIndex = xlNone
End Sub


"excelent" skrev:
 
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
 
Back
Top