Linking worksheet event codes

M

Mr. G.

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
 
K

Kassie

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
 
M

Mr. G.

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,
 
K

Kassie

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
 
M

Mr. G.

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?
 
K

Kassie

Hi

Shouldn't you code read:

Worksheets("EXP RPT").Protect password:="lindAP"
and
Worksheets("EXP RPT").Unprotect password:="lindAP"?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top