Click check box and fill range of cells with RED

[lockarde]

Good morning all,

I have a workbook that tracks ongoing jobs. I have a sheet for each month that tracks jobs requested during that time period. There are times when a job gets put on hold, and I'd like to have an "On Hold" checkbox that when I click it, it sets the fill color of the range of cells associated with that job to turn red. I have some basic code written, but am getting Subscript out of range error.

Sub Hold1_Click()

    With Sheet9
    Range("A4,T6").Interior.ColorIndex = RGB(255, 0, 0)
    End With

End Sub

This is sort of just a proof of concept, the range "A4:T6" is typically the range size of one job, however, sometimes there are extra rows included in the range due to extra "Notes". So ideally, I would have a checkbox next to each job, and it'd be able to find the end of the job, and color the range red (with the inverse being true as well, once a job is no longer on hold, unchecking the box would revert the format back to its original state). Some jobs have 3 or 4 rows of "notes", typically jobs only have 1 row of notes.

A typical job looks like the following:

Any help is greatly appreciated!

 

[lockarde]

Ok, it now runs through when I double-click on T1, but I get a type-mismatch error when it gets to

If Target.Value Like "Next Month" Then

Full Code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim Rng As Range, i As Integer
      Application.ScreenUpdating = False
    Dim mFormat1$, mFormat2$, msb$, msa$, msa2 As Byte
    Dim nws As Worksheet, ms_new$
    Dim mos As Integer, curdate As Double, moscheck As Integer, yearcheck As Integer, mostart As String
    i = Range("S2").Value

''Jobs on hold code:
   If Not Intersect(Target, Range("A4:A100")) Is Nothing Then
      If Target.Value Like "Item *" Then
         Set Rng = Intersect(Range(Target, Target.End(xlDown)).EntireRow, Range("A:T"))
         
         If Target.Interior.Color = vbRed Then
            Rng.Interior.Color = xlNone
            Rng.Columns(1).Interior.Color = 14277081
            If i = 0 Then
            i = 0
            Else
            i = i - 1
            End If
            Range("S2").Value = i
         Else
            Rng.Interior.Color = vbRed
            i = i + 1
            Range("S2").Value = i
         End If
      End If
      Cancel = True
   End If

''Adding Next Month Code:
If Not Intersect(Target, Range("T1")) Is Nothing Then
    If Target.Value Like "Next Month" Then
        curdate = Date
        moscheck = Month(curdate)
        yearcheck = year(curdate)
        
        mostart = Date - Day(Date) + 1
        
        msb = ActiveSheet.Name
        msa = Replace(msb, " ", ""): msa = Left(msa, InStr(msa, ".") - 1)
        msa2 = CByte(msa): msa2 = msa2 + 1
        mFormat1 = "1"
        
        mos = DateSerial(yearcheck, moscheck + 1, 1) - DateSerial(yearcheck, moscheck, 1)
        
        mFormat2 = mos
        
        ms_new = CStr(msa2) & Chr(46) & mFormat1 & Chr(32) & Chr(45) & Chr(32) & CStr(msa2) & Chr(46) & mFormat2
        Set nws = Sheets.Add(after:=Sheets(Sheets.Count)): nws.Name = ms_new
        
        Sheets(msb).Range("A1:T6").Copy: Sheets(ms_new).Range("A1").PasteSpecial Paste:=8, Operation:=xlNone
        Sheets(ms_new).Range("A1").PasteSpecial Paste:=-4104, Operation:=xlNone
        [C2].Select
        With Sheets(ms_new)
            .Range("v8").Value = curdate
            .Range("B5:T6").ClearContents
            .Columns("V").Hidden = True
        End With
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End If
    Cancel = True
End If
End Sub

 

[Fluff]

What do you have in T1?

 

[lockarde]

Ok I got it! I removed the line of code that caused the type-mismatch error and now it runs just fine. Was that because the Target contained a string? I'm just trying to understand what went wrong, and why it worked when I removed that line.

To answer your question just posted, T1 contains "Next Month"

 

[Fluff]

I have no idea why you got a Type Mismatch error, if T1 is "Next Month", unless you double-clicked T1 on a different sheet, which had something else.

 

[lockarde]

I have no idea why you got a Type Mismatch error, if T1 is "Next Month", unless you double-clicked T1 on a different sheet, which had something else.

Weird. Well I'll sift through and see if I can find the issue - but it works without that line, so I'm happy! Thanks again for all your help Fluff

 

[Fluff]

You're welcome & thanks for the feedback