| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
Hi - can anyone help please
I've tried Subject: 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
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 "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
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 "Vergel Adriano" wrote: 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 "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
Peta,
Change this line: Rows(lPreviousRow).Interior.ColorIndex = xlNone to become like this: If lPreviousRow < 0 then Rows(lPreviousRow).Interior.ColorIndex = xlNone End If "Peta" wrote: 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 "Vergel Adriano" wrote: 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 "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
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("A1:D5") Worksheet_Change (myRange) End Sub "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
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 "Joel" wrote: 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("A1:D5") Worksheet_Change (myRange) End Sub "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
worksheet_change colour of a row on change of cell
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 "Peta" wrote: 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 "Joel" wrote: 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("A1:D5") Worksheet_Change (myRange) End Sub "Peta" wrote: Hi - can anyone help please I've tried Subject: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| worksheet_change colour of a row on change of cell | Excel Programming | |||
| Recognizing cell change in a Sub Worksheet_Change procedure | Excel Programming | |||
| Recognizing cell change in a Sub Worksheet_Change procedure | Excel Programming | |||
| Using worksheet_change to change value of target cell?? | Excel Programming | |||
| change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
Hybrid Mode
