Altering The Code to Highlight an Entire Row

Posted by Sean on January 22, 2002 8:00 AM

When I insert the code below (from this page called, "hilite - http://www.cpearson.com/excel/download.htm") into a workbook it highlights only the active cell. How can I alter the code so that it highlights the entire row?

Thank you in advance,
Sean

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If

Target.Interior.ColorIndex = 6

Set OldCell = Target

End Sub

Posted by Juan Pablo G. on January 22, 2002 8:26 AM

Use

Target.EntireRow.Interior.ColorIndex = 6

Juan Pablo G.

Posted by Juan Pablo G. on January 22, 2002 8:27 AM

And OldCell.EntireRow.Interior.ColorIndex = xlColorIndexNone

Juan Pablo G.

Posted by Sean on January 22, 2002 8:38 AM

Hello Juan,

Thank you for your help it works great.

But when I click on Tools > Protection > Protect Sheet and then click a cell I get the following error message:

"Run-time error '1004':

Unable to set the ColorIndex property of the Interior class"

Thanks again for your help,
Sean

Posted by Juan Pablo G. on January 22, 2002 9:22 AM

Unprotect at the begining of the macro and re protect at the end.

Juan Pablo G.

Posted by Sean on January 22, 2002 10:20 AM

Sorry Juan Pablo but I have one more question. How do I do an uprotect/protect macro on this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range

If Not OldCell Is Nothing Then
OldCell.EntireRow.Interior.ColorIndex = xlColorIndexNone
End If

Target.EntireRow.Interior.ColorIndex = 43

Set OldCell = Target

End Sub

Thanks again,
Sean

Posted by Juan Pablo G. on January 22, 2002 1:43 PM

Use

ActiveSheet.Unprotect

and

ActiveSheet.Protect

Juan Pablo G.

Posted by Sean on January 23, 2002 8:51 AM

Thanks alot Juan Pablo, you have been a great help once again.

Sean