worksheet_change colour of a row on change of cell

[Guest]

Hi - can anyone help please
I've tried Subject: RE: More than 6 conditional Formats....VBA Coding Advise
please 1/5/2006 7:08 PM PST

By: JulieD In: microsoft.public.excel.newusers

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("K1:K20")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("A" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("A" & i & ":M" & i).Interior.ColorIndex = 12
Case "W"
Range("A" & i & ":M" & i).Interior.ColorIndex = 18
Case "X"
Range("A" & i & ":M" & i).Interior.ColorIndex = 22
Case "Y"
Range("A" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("A" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("A" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub

but doesn't work - I'm using XP - Excel 2003
it won't even debug - step into

any suggestion welcome
thanks

 

[Guest]

Peta,

You can't step into a function, or a subroutine that takes parameters. Try
this one out to change the color of a row when the selection is changed.

Dim lPreviousRow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If lPreviousRow <> Target.Row Then
Target.EntireRow.Interior.Color = vbBlue
Rows(lPreviousRow).Interior.ColorIndex = xlNone
lPreviousRow = Target.Row
End If
End Sub

 

[Guest]

For debugging add this function into the VBA. Your code doesn't run because
VBA doesn't recognized that Worksheet_Change requires parameter.

Main subroutines that are called from an excel spreadsheet cannot contain
parameters. Only secondarty subroutines can have parameters. subroutine
also cannot return parameters.

Functions can be have parameters and return values, but cannot write to
excel spreadsheets.

Beginners often havve these problems.

Sub abc()

Set myRange = Range("A15")

Worksheet_Change (myRange)

End Sub

 

[Guest]

Hi Mike

Yes the code is definitely in the correct worksheet.

I've also tried another suggestion I found on another thread:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 11 Then
Select Case .Value
Case "YES": .Resize(1, 11).Interior.ColorIndex = 25
Case "NO": .Resize(1, 11).Interior.ColorIndex = 2
Case "Maybe": .Resize(1, 11).Interior.ColorIndex = 2
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub


Also won't colour row

 

[Guest]

Vergel

thanks - I changed your code slightly to just a change event & it coloured
it ok but I got a run-time error 1004: Application defined or object defined
error at line
Rows(lPreviousRow).Interior.ColorIndex = xlNone

 

[Chip Pearson]

Peta,

As written, it is quite likely that you'll not get the correct matches
because you're doing vbBinaryCompare text comparisons, in which upper/lower
case does matter (e.g, "ABC" not equal "abc"). You can do one of (at least)
two things to remedy this. One is to put "Option Compare Text" on a line of
code immediately following the "Option Explicit" line. This will force all
text comparisons in the module to be case insensitive ("ABC"= "abc"). The
other way is to rewrite your Select Case statements as follows:

Select Case UCase(.Value)
Case "YES" : .Resize(1, 11).Interior.ColorIndex = 25
Case "NO" : .Resize(1, 11).Interior.ColorIndex = 2
Case "MAYBE" : .Resize(1, 11).Interior.ColorIndex = 2
End Select

This will convert the UPPER CASE .Value to UPPER CASE test values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Joel

I inserted your suggestion and came up with a run-time error 424 - object
required
Dim lPreviousRow As Long
Dim myrange As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Set myrange = Range("A1:N25")
Worksheet_Change (myrange)
If lPreviousRow <> Target.Row Then
Target.EntireRow.Interior.Color = vbBlue
Rows(lPreviousRow).Interior.ColorIndex = xlNone
lPreviousRow = Target.Row
End If
End Sub

 

[Guest]

thanks Chip - that did get the colouring working however it colours columns K
to U (11-21)
would altering .Resize(1, 11).Interior.ColorIndex = 25 to
..Resize(A, K).Interior.ColorIndex = 25 work?

thanks in advance

 

[Dave Peterson]

..resize() expects numbers (number of rows, number of columns)

There's another property that you can use to specify where to start.
..Resize(1, 11).Interior.ColorIndex = 25
becomes
..offset(0,-10).Resize(1, 11).Interior.ColorIndex = 25

Since you're looking at column K, then .offset(0,-10) says to stay on that same
row (with the 0), but go 11 columns to the left (-11).

Then the .resize kicks in: Make the shaded range 1 row by 11 columns.

 

[Guest]

Petra: This is the code I used to debug the problem. there is a problem when
the row i1 th eprevious row is then 0. the macro was failing because row 0
doesn't exist.

Dim lPreviousRow As Long
Sub test_macro()
Set myrange = Range("A1:N25")
Worksheet_Change (myrange)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If lPreviousRow <> Target.Row Then
Target.EntireRow.Interior.Color = vbBlue
If lPreviousRow <> 0 Then
Rows(lPreviousRow).Interior.ColorIndex = xlNone
End If
lPreviousRow = Target.Row
End If
End Sub

 

[Guest]

Peta,

Change this line:

Rows(lPreviousRow).Interior.ColorIndex = xlNone

to become like this:

If lPreviousRow <> 0 then
Rows(lPreviousRow).Interior.ColorIndex = xlNone
End If

 

[Tom Ogilvy]

When Dave said
go 11 columns to the left (-11).

believe he meant

go 11 columns to the left (-10). (per his original example using offset)

whether you view that as 11 columns or 10, it puts you in column A. The
counting for offset starts with the next column to the left or right
depending on sign (or up or down if using the row argument).

?Range("K1").Offset(0,-10).Address
$A$1

 

[Dave Peterson]

Thanks for the correction/amplification.

When Dave said
go 11 columns to the left (-11).

believe he meant

go 11 columns to the left (-10). (per his original example using offset)

whether you view that as 11 columns or 10, it puts you in column A. The
counting for offset starts with the next column to the left or right
depending on sign (or up or down if using the row argument).

?Range("K1").Offset(0,-10).Address
$A$1

 

[Guest]

Dave I'm still getting errors

Sub Worksheet_Change(ByVal target As Range)
On Error GoTo Err_Handler
If target.Address <> "E" Then Exit Sub

If Not (Intersect(target, Range("E:E"))) Is Nothing Then
Application.EnableEvents = False

Select Case target.Value
'Select Case (.Value) 'invalid or unqualified reference
Case "commenced":
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 25
'with .Offset... gives error - "invalid or unqualified reference" - (with a
period)
'without a period - as above - gives error: "sub or function not defined"
Case "pending":
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 12
'other cases in here
Case Else
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = xlnone
End Select
End If
Err_Handler:
Application.EnableEvents = True
End Sub

thanks for your patience

regards
peta

 

[Dave Peterson]

There were dots in front of those suggested .offset() lines. Those dots meant
that they belonged to something--either the object right in front of them--like:

Target.offset(.....

Or they could belong to the object in the previous With statement:

Option Explicit
Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub

On Error GoTo Err_Handler
Application.EnableEvents = False
With Target
Select Case LCase(.Value)
Case "commenced":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 25
Case "pending":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 12
'other cases in here
Case Else
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = xlNone
End Select
End With

Err_Handler:
Application.EnableEvents = True
End Sub

 

[Guest]

Terrific - thanks a lot

There were dots in front of those suggested .offset() lines. Those dots meant
that they belonged to something--either the object right in front of them--like:

Target.offset(.....

Or they could belong to the object in the previous With statement:

Option Explicit
Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub

On Error GoTo Err_Handler
Application.EnableEvents = False
With Target
Select Case LCase(.Value)
Case "commenced":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 25
Case "pending":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 12
'other cases in here
Case Else
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = xlNone
End Select
End With

Err_Handler:
Application.EnableEvents = True
End Sub