Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Applying code being used in a worksheet to the entire workbo

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •