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!

 

[Fluff]

How about

Sheet9.Range("A4,T6").Interior.Color = RGB(255, 0, 0)

or

Sheet9.Range("A4:T6").Interior.Color = RGB(255, 0, 0)

 

[lockarde]

Thanks for your reply Fluff! I was writing a reply as your response came in - I got the same code you posted, and it works great! Could you help with "undoing" that fill when I uncheck the box? I'm not sure how to track the previous formatting in order to revert it back once the job is no longer on hold

 

[Fluff]

Which of the two are you using?

 

[lockarde]

I'm using something similar to the second version you posted,

Sub Hold1_Click()

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

 

[Fluff]

Thinking about it are you using a Form Control checkbox, or an ActiveX one?
And is it on Sheet9?

 

[lockarde]

It's an ActiveX checkbox, and it is on Sheet9. Eventually, I'd like to have a checkbox for each job - and that's where it gets tricky if the job has more than the typical 3 rows, and the number of jobs fluctuates month to month.

 

[Fluff]

Rather than adding/deleting checkboxes depending on the number of jobs, how about using a doubleclick event.
So, for instance you doubleclick A4 & the cells turn red?

Can you post some sample data using the XL2BB add-in that shows jobs with variable number of rows?

 

[Fluff]

This will work for your current setup.

Sub Hold1_Click()
   Dim Clr As Long
   With Sheet9
      If .Hold1.Value Then
         Clr = .Range("A4").Interior.Color
         .Range("A4:T6").Interior.Color = RGB(255, 0, 0)
         .Range("A4").Interior.Color = Clr
      Else
         .Range("A4:T6").Interior.Color = Clr
      End If
   End With
End Sub

I have left A4 with the current fill as it needs to be stored somewhere, but it can be changed to another cell

 

[lockarde]

I actually like the double-click idea alot, that way I don't have to keep adding a checkbox for each new job. It took me a second to get the XL2BB all sorted, but the following is what I captured, with sensitive information removed. I'm not sure why the columns became so large? I put a screenshot at the bottom so you can see what it looks like for me.

Daily Engineering Reporting.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Engineering Request Designs					Total Engineering requests Sheet1:			2		TOTAL WORKDAYS:		22			Completed Engineering requests Sheet1:				0/2
2	Updated on:		2/28/2020	12:52 PM
3	Item(s)	Location	Employee	Job ID	Company	Job Value	Net Price	Est Hours	Act Hours	Request Date	Comp Date	L	W	H	Qty.	Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
4	Item 1
5	Notes:	Standard request, one note with basic job details. Job completed with no updates/change orders required. Completeion date gets filled in and counters at top track
6
7	Item(s)	Location	Employee	Job ID	Company	Job Value	Crate Price	Est Hours	Act Hours	Request Date	Comp Date	L	W	H	Qty.	Crate Style	Mil Spec?	Prod Job?	Concept?	Design/BOM?
8	Item 1
9	Notes:	Initally starts as standard request
10	Notes:	As job progress, notes possibly added detailing communication with customer
11	Notes:	Last note on a job such as this states current "status" of job. i.e "Customer requested changes - conference call scheduled 3/2/20"
Sheet1

Cell Formulas
Range	Formula
F1	F1	="Total Engineering requests " & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) & ": "
I1	I1	= COUNTIF(A:A,A4)
M1	M1	=NETWORKDAYS(V8,EOMONTH(V8,0))
P1	P1	="Completed Engineering requests " & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) & ": "
T1	T1	=COUNT($K:$K)&"/"&I1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T1	Expression	=COUNT($K:$K)=$I$1	text	NO
T1	Expression	=COUNT($K:$K)<$I$1	text	NO