Prev: Working in Sheet2 with data from Sheet1
Next: HELP: Unable to clear values for control of type text in a userfor
From: L. Howard Kittle on 1 Jun 2010 19:00
If you want to highlight other than the first to the 17th you could try
something like this.

Highlights the row from column B to K which you can change by tweeking the
code and only works in the Range("B8:K22") that is set to Data in the code,
which you can also change.

You can probably figure out the Offset/Resize changes to suit your chosen
range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim cells As Range
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column > 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 36 '26

End Sub

HTH
Regards,
Howard


"JingleRock" <cegrob(a)comcast.net> wrote in message
news:fede0c39-a26b-401e-b1d0-d18238c59dd5(a)f14g2000vbn.googlegroups.com...
>I am using David McRitchie's code for changing color of entire row
> based on contents based on a specified cell text value:
> 'Target.EntireRow.Interior.ColorIndex = 36'.
> This works fine; however, I only want to change color in the first 17
> cells in each of the affected rows. How do I do this?
> Also, I am confused: do I want the stmt 'Application.EnableEvents =
> True' at the top of my coding in the 'Worksheet_Change' event coding
> (occupies the Sheet1 Module)?


First  |  Prev  | 
Pages: 1 2
Prev: Working in Sheet2 with data from Sheet1
Next: HELP: Unable to clear values for control of type text in a userfor