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
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
..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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson |
#10
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 |
#11
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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 -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... .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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
Thanks for the correction/amplification.
Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... .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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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" wrote: ..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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
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 Peta wrote: 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" wrote: ..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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet_change colour of a row on change of cell
Terrific - thanks a lot
"Dave Peterson" wrote: 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 Peta wrote: 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" wrote: ..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. Peta wrote: 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 "Chip Pearson" wrote: 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) "Peta" wrote in message ... 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 "Mike" wrote: Peta, There's nothing wrong with the code, where have you put it? You should have right clicked the sheet tab - view code and pasted it in there. Mike "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 -- Dave Peterson -- Dave Peterson |
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) |