Change color of row (Col A thru Col Q) based on text value in Col J
 

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)?

Change color of row (Col A thru Col Q) based on text value in Col J
 

One way:

Target.EntireRow.resize(1,17).Interior.ColorIndex = 36

JingleRock wrote:

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)?

--

Dave Peterson

Change color of row (Col A thru Col Q) based on text value in ColJ
 

Dave,

Thanks for your response and suggestion.
For some reason, my Worksheet_Change event is not working at all.
For example, I commented-out my Sheet1 Module and replaced it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 3
End Sub

Then, I enter text in various cells of Sheet1 and there is zero color
change. Any ideas?

Change color of row (Col A thru Col Q) based on text value in ColJ
 

The Change Event is working now. Again, any ideas?

Change color of row (Col A thru Col Q) based on text value in Col J
 

My bet is that you didn't put the code in the correct module.

In excel, Rightclick on the worksheet tab that should have this behavior.
Select view code
paste your procedure into the code window that just opened.

And make sure that macros are enabled and events are enabled, too.

Inside the VBE:
hit ctrl-g (to see the immediate window)
type:
application.enableevents = true
and hit enter.

Then back to excel to test.

JingleRock wrote:

Dave,

Thanks for your response and suggestion.
For some reason, my Worksheet_Change event is not working at all.
For example, I commented-out my Sheet1 Module and replaced it with:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 3
End Sub

Then, I enter text in various cells of Sheet1 and there is zero color
change. Any ideas?

--

Dave Peterson

Change color of row (Col A thru Col Q) based on text value in Col J
 

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" wrote in message
...
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)?