Linking worksheet event codes
The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
Hi
Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
Hi Kassie,
Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
Hi
I think what you did, was that you copied the complete macro, i.e. from the name down to the end, into your code. You should only have copied the part from Cells.FormatConditions.Delete to the end into your routine "Mr. G." wrote: Hi Kassie, Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
Hi Kassie,
Unfortunately, I still can't get it to work. But I think I may have found part of the problem. If you notice my worksheet code includes a protection password (lindAP). Populating (or unpopulating) certain cells activates this code which unprotects the worksheet in order to unhide (or hide) partiular rows and before reverting back to its protected status. During my (many) unsuccessful attempts, I did discover that when I manually unprotect this worksheet, the function to highlight rows operates. However, I can't get my rows to unhide. Any suggestions, or possible solution? "Kassie" wrote: Hi I think what you did, was that you copied the complete macro, i.e. from the name down to the end, into your code. You should only have copied the part from Cells.FormatConditions.Delete to the end into your routine "Mr. G." wrote: Hi Kassie, Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
Hi
Shouldn't you code read: Worksheets("EXP RPT").Protect password:="lindAP" and Worksheets("EXP RPT").Unprotect password:="lindAP"? "Mr. G." wrote: Hi Kassie, Unfortunately, I still can't get it to work. But I think I may have found part of the problem. If you notice my worksheet code includes a protection password (lindAP). Populating (or unpopulating) certain cells activates this code which unprotects the worksheet in order to unhide (or hide) partiular rows and before reverting back to its protected status. During my (many) unsuccessful attempts, I did discover that when I manually unprotect this worksheet, the function to highlight rows operates. However, I can't get my rows to unhide. Any suggestions, or possible solution? "Kassie" wrote: Hi I think what you did, was that you copied the complete macro, i.e. from the name down to the end, into your code. You should only have copied the part from Cells.FormatConditions.Delete to the end into your routine "Mr. G." wrote: Hi Kassie, Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
You're absolutely right! It works!! Thank you so much.
"Kassie" wrote: Hi Shouldn't you code read: Worksheets("EXP RPT").Protect password:="lindAP" and Worksheets("EXP RPT").Unprotect password:="lindAP"? "Mr. G." wrote: Hi Kassie, Unfortunately, I still can't get it to work. But I think I may have found part of the problem. If you notice my worksheet code includes a protection password (lindAP). Populating (or unpopulating) certain cells activates this code which unprotects the worksheet in order to unhide (or hide) partiular rows and before reverting back to its protected status. During my (many) unsuccessful attempts, I did discover that when I manually unprotect this worksheet, the function to highlight rows operates. However, I can't get my rows to unhide. Any suggestions, or possible solution? "Kassie" wrote: Hi I think what you did, was that you copied the complete macro, i.e. from the name down to the end, into your code. You should only have copied the part from Cells.FormatConditions.Delete to the end into your routine "Mr. G." wrote: Hi Kassie, Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
That was all my pleasure Mr. G! I'm glad you could sort it out!
"Mr. G." wrote: You're absolutely right! It works!! Thank you so much. "Kassie" wrote: Hi Shouldn't you code read: Worksheets("EXP RPT").Protect password:="lindAP" and Worksheets("EXP RPT").Unprotect password:="lindAP"? "Mr. G." wrote: Hi Kassie, Unfortunately, I still can't get it to work. But I think I may have found part of the problem. If you notice my worksheet code includes a protection password (lindAP). Populating (or unpopulating) certain cells activates this code which unprotects the worksheet in order to unhide (or hide) partiular rows and before reverting back to its protected status. During my (many) unsuccessful attempts, I did discover that when I manually unprotect this worksheet, the function to highlight rows operates. However, I can't get my rows to unhide. Any suggestions, or possible solution? "Kassie" wrote: Hi I think what you did, was that you copied the complete macro, i.e. from the name down to the end, into your code. You should only have copied the part from Cells.FormatConditions.Delete to the end into your routine "Mr. G." wrote: Hi Kassie, Thank you for the prompt resonse. Unfortunately, I may have misunderstood your instuctions because I'me receiving the following Compile Error message: Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's code to the end of mine (btwn "End If" and "End Sub")? Because that's what I did and subsequently, the row shading function appeared to work but when I performed a function that suppose to activate my event code (unhiding rows automatically) I received the earlier mentioned Error message. When viewing the worksheet code, the last "End If" was highlighted. Can you tell/show me what I need to do? Thank you, "Kassie" wrote: Hi Simply call his routine from yours, just before you end your routine Worksheet_SelectionChange End Sub Or copy and paste the body into your routine, where you want it to execute; Or vice versa of course, depending on which one you want to execute first "Mr. G." wrote: The (row shading) worksheet event code furnished by B.Phillips was exactly what I've been looking for. However, since I have already have a worksheet event code running (developed by Julie D) when the user opens up the spreadsheet, does anyone know if it's possible to still incorporate/link his code with mine? (Mr. Phillips) Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With With .Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 2 End With End With .FormatConditions(1).Interior.ColorIndex = 20 End With (Mine) Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F13:F15") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F12:F15") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1" On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic For Each c In Range("F18:F20") If c.Value = "" And c.Offset(0, 46) = 0 Then Sheets("EXP RPT").Unprotect ("lindAP") c.EntireRow.Hidden = True i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next For Each c In Range("F17:F20") If c.Value < "" Then Sheets("EXP RPT").Unprotect ("lindAP") c.Offset(1, 0).EntireRow.Hidden = False i = c.Row Sheets("EXP RPT").Protect ("lindAP") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com