Applying code being used in a worksheet to the entire workbo

[BenNV]

Hello,

I'm trying to apply code currently being used for individual worksheets to the whole workbook. If the worksheet is not active and the below conditions are met, then I get a runtime error. Could you take a look at my code and see what I need to change and where I should put the code:


Private Sub Worksheet_Calculate()

Dim lLastRow As Long, Data1, ColE, ColG, ColH, COlI, lRow As Long
Dim shtThis As Worksheet, bThisRow As Boolean
Dim ranj As String

Data1 = Cells(1, 4).Value

Set shtThis = ActiveSheet

With Cells(5, 1).CurrentRegion
lLastRow = .Rows.Count + .Row - 1
End With


For lRow = 5 To lLastRow
ColE = Cells(lRow, 5).Value
ColF = Cells(lRow, 6).Value
ColG = Cells(lRow, 7).Value
ColH = Cells(lRow, .Value
COlI = Cells(lRow, 9).Value

bThisRow = False 'pay
cThisRow = False 'pay nothing


Select Case ColG
Case "DDD"
If ColH = Data1 Or COlI = Data1 Then _
cThisRow = True
Case "OO"
If ColE = Data1 Then _
bThisRow = True
Case "RRR"
Select Case ColF
Case "X"
If COlI = Data1 Then _
cThisRow = True
Case "O"
If ColH = Data1 Then _
cThisRow = True
End Select
Case "II"
Select Case ColF
Case "X"
If COlI = Data1 Then _
bThisRow = True
Case "O"
If ColH = Data1 Then _
bThisRow = True
End Select
Case "RKK"
Select Case ColF
Case "X"
If ColH = Data1 Then _
bThisRow = True
Case "O"
If COlI = Data1 Then _
bThisRow = True
End Select
Case "BOX"
Select Case ColF
Case "X"
If ColH = Data1 Then _
cThisRow = True
Case "O"
If COlI = Data1 Then _
cThisRow = True
End Select
End Select

With shtThis.Rows(lRow)
If bThisRow Then
'Cells(lRow, 12).Select
Range("A" & lRow & ":L" & lRow).Select
'ranj = "A" & lRow & ":L" & lRow
' Object.Range (ranj)
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

'Else
' With .Interior
' .ColorIndex = xlNone
' .Pattern = xlNone
' End With
End If

If cThisRow Then
Range("A" & lRow & ":L" & lRow).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

End With
Next


End Sub

 

[Legacy 98055]

If you have any problems, here is your finished code as is with the new edit.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim lLastRow As Long, Data1, ColE, ColG, ColH, COlI, lRow As Long
Dim shtThis As Worksheet, bThisRow As Boolean
Dim ranj As String

For Each shtThis In Worksheets


Data1 = Cells(1, 4).Value
Debug.Print shtThis.Name


With Cells(5, 1).CurrentRegion
lLastRow = .Rows.Count + .Row - 1
End With


For lRow = 5 To lLastRow
ColE = Cells(lRow, 5).Value
ColF = Cells(lRow, 6).Value
ColG = Cells(lRow, 7).Value
ColH = Cells(lRow, .Value
COlI = Cells(lRow, 9).Value

bThisRow = False 'pay
cThisRow = False 'pay nothing


Select Case ColG
Case "DNT"
If ColH = Data1 Or COlI = Data1 Then _
cThisRow = True
Case "OT"
If ColE = Data1 Then _
bThisRow = True
Case "RKO"
Select Case ColF
Case "C"
If COlI = Data1 Then _
cThisRow = True
Case "P"
If ColH = Data1 Then _
cThisRow = True
End Select
Case "KI"
Select Case ColF
Case "C"
If COlI = Data1 Then _
bThisRow = True
Case "P"
If ColH = Data1 Then _
bThisRow = True
End Select
Case "RKI"
Select Case ColF
Case "C"
If ColH = Data1 Then _
bThisRow = True
Case "P"
If COlI = Data1 Then _
bThisRow = True
End Select
Case "KO"
Select Case ColF
Case "C"
If ColH = Data1 Then _
cThisRow = True
Case "P"
If COlI = Data1 Then _
cThisRow = True
End Select
End Select


With shtThis.Rows(lRow)
If bThisRow Then
'Cells(lRow, 12).Select
Range("A" & lRow & ":L" & lRow).Select
'ranj = "A" & lRow & ":L" & lRow
' Object.Range (ranj)
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

'Else
' With .Interior
' .ColorIndex = xlNone
' .Pattern = xlNone
' End With
End If

If cThisRow Then
Range("A" & lRow & ":L" & lRow).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

End With
Next
Next
End Sub

 

[BenNV]

Still no joy, doesn't seem to go through the procedure if the worksheet is not active.

 

[Legacy 98055]

I'll try one more time.
I think that there may be statements which are only referring to the activesheet.
I will check.
Tom