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

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

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