Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a row in grey
On 30 April, Nigel kindly gave me the following code to highlight a
row in Excel which contains the text "Robert". (NIGEL, forgive me for not replying to you personally; I have tried but my web browser tells me I cannot support the correct "cookies" when I click on the REPLY button). The code is as follows and works perfectly: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Sh With Target If .Cells.Text = "Robert" Then Rows(Target.Row).Interior.ColorIndex = 15 Else Rows(Target.Row).Interior.ColorIndex = xlNone End If End With End With End Sub However I now need the statement to be amended so that it not only highlights rows containing "Robert" in grey, but those containing "Jenny" in a lighter shade of grey, thus visually differentiating "Jenny" rows from the "Robert" rows. I tried adding another "IF" statement underneath but it doesn't work. Also, can the code be amended further so that it only searches for "Robert" or "Jenny" in Row "E". Thanks again. Kind regards paddymichelle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a row in grey
Try the following:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Sh With Target If .Cells.Text = "Robert" Then Rows(Target.Row).Interior.ColorIndex = 15 elseif .cells.text = "Jenny" Then Rows(Target.Row).Interior.ColorIndex = (Enter number for color here) Else: Rows(Target.Row).Interior.ColorIndex = xlNone End If End With End With End Sub Ian M wrote: *On 30 April, Nigel kindly gave me the following code to highlight a row in Excel which contains the text "Robert". (NIGEL, forgive m for not replying to you personally; I have tried but my web browse tells me I cannot support the correct "cookies" when I click on the REPLY button). The code is as follows and works perfectly: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Sh With Target If .Cells.Text = "Robert" Then Rows(Target.Row).Interior.ColorIndex = 15 Else Rows(Target.Row).Interior.ColorIndex = xlNone End If End With End With End Sub However I now need the statement to be amended so that it not only highlights rows containing "Robert" in grey, but those containing "Jenny" in a lighter shade of grey, thus visually differentiating "Jenny" rows from the "Robert" rows. I tried adding another "IF" statement underneath but it doesn' work. Also, can the code be amended further so that it only searches for "Robert" or "Jenny" in Row "E". Thanks again. Kind regards paddymichelle -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a row in grey
Sometimes, too many if's gets difficult to decipher.
You may want to try "Select Case". Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim myColorIndex As Long 'only one cell at a time If Target.Cells.Count 1 Then Exit Sub Select Case LCase(Target.Value) Case Is = "robert": myColorIndex = 16 Case Is = "jenny": myColorIndex = 14 Case Else: myColorIndex = xlNone End Select Target.EntireRow.Interior.ColorIndex = myColorIndex End Sub (change the numbers to match your workbook's colors.) Ian M wrote: On 30 April, Nigel kindly gave me the following code to highlight a row in Excel which contains the text "Robert". (NIGEL, forgive me for not replying to you personally; I have tried but my web browser tells me I cannot support the correct "cookies" when I click on the REPLY button). The code is as follows and works perfectly: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Sh With Target If .Cells.Text = "Robert" Then Rows(Target.Row).Interior.ColorIndex = 15 Else Rows(Target.Row).Interior.ColorIndex = xlNone End If End With End With End Sub However I now need the statement to be amended so that it not only highlights rows containing "Robert" in grey, but those containing "Jenny" in a lighter shade of grey, thus visually differentiating "Jenny" rows from the "Robert" rows. I tried adding another "IF" statement underneath but it doesn't work. Also, can the code be amended further so that it only searches for "Robert" or "Jenny" in Row "E". Thanks again. Kind regards paddymichelle -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I "grey out" a row in excel | Excel Discussion (Misc queries) | |||
Worksheet went grey! | Excel Worksheet Functions | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
Grey box | Charts and Charting in Excel | |||
Grey out | Excel Programming |