Prev: Working in Sheet2 with data from Sheet1
Next: HELP: Unable to clear values for control of type text in a userfor
From: JingleRock on 1 Jun 2010 14:25
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)?
From: Dave Peterson on 1 Jun 2010 14:32
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
From: JingleRock on 1 Jun 2010 16:30
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?

From: JingleRock on 1 Jun 2010 17:01
The Change Event is working now. Again, any ideas?

From: Dave Peterson on 1 Jun 2010 17:27
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
 |  Next  |  Last
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