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

 

[Dave Hawley]

Hi BenNV


Right click on the Excel icon, top left next to "File", select "View Code" and paste you code in here:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

 

[BenNV]

Thank you. I've done that but it appears some of the properties may need to be changed as this does not apply for workbooks. Can you help?

 

[BenNV]

Can anyone help on this, please?

I am trying to make the conditions apply to the whole workbook for each worksheet, even if the worksheet is inactive.

I put the code where Dave suggested but think certain areas of the code need changing.

 

[Legacy 98055]

Where and what errors are you recieving?
Email your Wb if you want. TsTom@hotmail.com
Tom
This message was edited by TsTom on 2002-04-10 01:42

 

[Legacy 98055]

Hi Ben
I sent this to the wrong e-mail address and am not sure if yours is correct either.
Anyway,

Try this and see if it solves your problem.
Replace the following lines of code in your Workbook_SheetCalculate event

Replace:
Dim shtThis As Workbook, bThisRow As Boolean
with
Dim shtThis As Worksheet, bThisRow As Boolean

Replace:
Set shtThis = ActiveWorkbook
with
Set shtThis = sh

Let me know if this works.
Thanks,
Tom

 

[BenNV]

Tom, thanks. But only seems to work for the active sheet, not any of the other inactive sheets, ie. if a sheet is inactive and the conditions meet then nothing happens.

 

[Legacy 98055]

I may be missing your objective here, Ben.
You want all sheets to do what?
I do not know exactly how your workbook functions. Do you want the same process which is occuring in the active sheet to duplicate in every sheet?
If that is the case then that would not be a problem...
Sorry if I missed the mark here.
Let me know,
Tom

 

[BenNV]

Tom, in all of the sheets in the workbook, I need the same process to occur. So say the conditions are met in an inactive worksheet then I need the cells to highlight. At the moment it only works on the active worksheet. So, should the conditions that I specified be met in an inactive worksheet, nothing happens.

 

[Legacy 98055]

If the former is the case then do this:

Replace:
Set shtThis = sh
With:
For Each shtThis In Worksheets

Right above End Sub add this:
Next

This will loop through every sheet in the workbook everytime any sheet calculates.

Tom