Reputation: 71

Using offset for multiple criteria

I have 5 lines of code that I need to reproduce about 180 times. With each line (or group of lines) I need do adjust just a few numbers. It is super tedious to do with replace. Is there a way to do this with offset or something like that? Here is the code:

If Target.Address = "$E$7" Then
    MsgBox Worksheets("Budget Hours").Range("F3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("F6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("F7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("F8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("F9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("F10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$8" Then
    MsgBox Worksheets("Budget Hours").Range("G3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("G6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("G7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("G8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("G9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("G10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$9" Then
    MsgBox Worksheets("Budget Hours").Range("H3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("H6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("H7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("H8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("H9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("H10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$10" Then
    MsgBox Worksheets("Budget Hours").Range("I3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("I6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("I7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("I8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("I9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("I10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$11" Then
    MsgBox Worksheets("Budget Hours").Range("J3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("J6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("J7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("J8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("J9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("J10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$12" Then
    MsgBox Worksheets("Budget Hours").Range("K3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("K6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("K7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("K8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("K9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("K10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$13" Then
    MsgBox Worksheets("Budget Hours").Range("L3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("L6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("L7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("L8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("L9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("L10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$14" Then
    MsgBox Worksheets("Budget Hours").Range("M3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("M6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("M7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("M8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("M9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("M10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$15" Then
    MsgBox Worksheets("Budget Hours").Range("N3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("N6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("N7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("N8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("N9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("N10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$16" Then
    MsgBox Worksheets("Budget Hours").Range("O3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("O6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("O7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("O8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("O9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("O10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$17" Then
    MsgBox Worksheets("Budget Hours").Range("P3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("P6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("P7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("P8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("P9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("P10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$18" Then
    MsgBox Worksheets("Budget Hours").Range("Q3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("Q6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("Q7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("Q8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("Q9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("Q10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$19" Then
    MsgBox Worksheets("Budget Hours").Range("R3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("R6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("R7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("R8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("R9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("R10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$20" Then
    MsgBox Worksheets("Budget Hours").Range("S3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("S6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("S7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("S8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("S9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("S10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$21" Then
    MsgBox Worksheets("Budget Hours").Range("T3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("T6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("T7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("T8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("T9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("T10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$22" Then
    MsgBox Worksheets("Budget Hours").Range("U3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("U6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("U7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("U8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("U9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("U10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$23" Then
    MsgBox Worksheets("Budget Hours").Range("V3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("V6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("V7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("V8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("V9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("V10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$24" Then
    MsgBox Worksheets("Budget Hours").Range("W3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("W6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("W7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("W8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("W9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("W10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$25" Then
    MsgBox Worksheets("Budget Hours").Range("X3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("X6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("X7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("X8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("X9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("X10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$26" Then
    MsgBox Worksheets("Budget Hours").Range("Y3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("Y6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("Y7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("Y8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("Y9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("Y10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value
    ElseIf Target.Address = "$E$27" Then
    MsgBox Worksheets("Budget Hours").Range("Z3").Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Range("Z6").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Range("Z7").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Range("Z8").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Range("Z9").Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Range("Z10").Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value

So the 6, 7, 8, 9, and 10 are replaced with 15, 16, 17, 18, and 19 in the next grouping. Then there is another 6 sets of the above code. Is there a way to implement some sort of offset so I can only adjust that and the rest will balance? It will always be five consecutive numbers so a single offset to would work to get to the correct starting point. An aside - the offset will not always be equal to 9 (ie. 6 becoming 15). One of the offsets will be a different number so I'd like to be able to adjust accordingly.

Upvotes: 0

Answers (3)

eyeScream

Reputation: 71

I have adjusted for what @TimWilliams said above. I now tried to make it work for my second grouping but it doesn't quite work. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate

Dim ws1 As Worksheet, ws2 As Worksheet, list As Range, msg, i As Long

Set ws1 = Worksheets("Budget Hours")
Set ws2 = Worksheets("Schedule")

'Phase 01
Dim rngPH1 As Range, rng1 As Range, rng2 As Range
Set rngPH1 = ws2.Range("E7:E27")
Set rng1 = ws1.Range("E6:E10")
Set rng2 = rng1.Offset(0, Target.Row - 6)

For Each list In rngPH1
    msg = rng2.EntireColumn.Cells(3).Value & vbNewLine
    For i = 1 To rng1.Cells.Count
        msg = msg & vbNewLine & rng1.Cells(i).Value & " - " & rng2.Cells(i).Value & " Hours"
    Next i
Next list

MsgBox msg, , ws1.Range("E5").Value

'Phase 02
Dim rngPH2 As Range, rng3 As Range, rng4 As Range
Set rngPH2 = ws2.Range("E43:E63")
Set rng3 = ws1.Range("E15:E19")
Set rng4 = rng3.Offset(0, Target.Row - 36)

For Each list In rngPH2
    msg = rng4.EntireColumn.Cells(3).Value & vbNewLine
    For i = 1 To rng1.Cells.Count
        msg = msg & vbNewLine & rng3.Cells(i).Value & " - " & rng4.Cells(i).Value & " Hours"
    Next i
Next list

MsgBox msg, , ws1.Range("E14").Value

End Sub

So my problem is that I want the Phase 01 stuff to only work on the range Schedule("E7:E27"). The Phase 02 stuff I want only to work on the range Schedule("E43:E63"). I thought by using rngPH1 and rngPH2 I could accomplish this but it didn't work. If I click in say Schedule("E8"), it brings up the correct stuff from ws1 perfectly, but then carries on to the Phase 02 stuff which I don't want. How do I get it to do the Phase 01 stuff only on the range rngPH1 and the Phase 02 stuff only on the range rngPH2?

Upvotes: 0

Tim Williams

Reputation: 166391

I'm unsure what triggers the next "grouping" but here's a different appraoch.

Untested:

Dim ws As Worksheet, rng1 As Range, rng2 As Range, msg, i As Long

Set ws = Worksheets("Budget Hours")

Set rng1 = ws.Range("E6:E10")
Set rng2 = rng1.Offset(0, target.Row - 6)

msg = rng2.EntireColumn.Cells(3).Value & vbNewLine
For i = 1 To rng1.Cells.count
    msg = msg & vbNewLine & rng1.Cells(i).Value & " - " & rng2.Cells(i).Value & " Hours"
Next i

MsgBox msg, , ws.Range("E5").Value

Upvotes: 1

Warcupine

Reputation: 4640

Assuming they will continue to all follow the same pattern, where target.row correlates to the ending column this should work.

Take the .row from target and subtract one to get your new column.

    MsgBox Worksheets("Budget Hours").Cells(3, target.Row - 1).Value & vbNewLine _
        & vbNewLine & Worksheets("Budget Hours").Range("E6").Value & " - " & Worksheets("Budget Hours").Cells(6, target.Row - 1).Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E7").Value & " - " & Worksheets("Budget Hours").Cells(7, target.Row - 1).Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E8").Value & " - " & Worksheets("Budget Hours").Cells(8, target.Row - 1).Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E9").Value & " - " & Worksheets("Budget Hours").Cells(9, target.Row - 1).Value & " Hours" _
        & vbNewLine & Worksheets("Budget Hours").Range("E10").Value & " - " & Worksheets("Budget Hours").Cells(10, target.Row - 1).Value & " Hours" _
            , , Worksheets("Budget Hours").Range("E5").Value

Now you shouldn't need any if statements.

Upvotes: 0

Using offset for multiple criteria

Answers (3)

Related Questions