Trying to create multi IF and Then VBA code to search for criteria and fill a cell with text.

[JDSA84]

I'm trying to make a code that will look and see if columns meet certain criteria then Q= a value in text. Like below. But I need to do this in each cell from 2 to 500 and if Q2 = 4 then R2 = 5 S2 = 3 and T2 = 1 or 2

If [L2] = "" Or [L2] = "/" And CBool((Sgn([C2] - 1) + Sgn(30 - [C2])) > 0) And CBool((Sgn([E2] - 1) + Sgn(3556 - [E2])) > 0) Then [Q2].Value = "4"

The above code works but will be to much data for VBA with what I need it to do.

Criteria I'm following is
4 if L= Blank or /, C = 1 through 30, and E = 1 through 500 or C= 1 through 12 and E = 1 through 999
5 if M= Blank or /, C = 1 through 30, and E = 1 through 500 or C= 1 through 12 and E = 1 through 999
6 if L= Blank or /, C = 10 through 12, and E = 1000 through 5000
7 if L= Blank or /, C = 10 through 12, and E = 1000 through 8000
9 if L= Blank or /, C = 24 through 30, and E = 500 through 2000 or C = 31 through 36 and E = 1 though 500
13 if L= Blank or /, C = 31 through 36, and E = 500 through 2000 or C = 37 through 42 and E = 1 though 500
13 if M= Blank or /, C = 24 through 30, and E = 500 through 2000 or C = 31 through 38 and E = 1 though 500
14 if L= Blank or /, C = 38 through 52, and E = 1 through 2000 or C = 31 through 38 and E = 1 though 500
14 if M= Blank or /, C = 38 through 42, and E = 500 through 2000
15 if L= Blank or /, C = 52 through 180, and E = 1 through 8000
15 if M= Blank or /, C = 43 through 180, and E = 1 through 8000
3 if N= Blank or /, C = 1 through 20, and E = 1 through 8000 or C = 20 through 30 and E = 1 through 500
1 or 2 if O = Blank or /, C = 1 through 20, and E = 1 through 8000 or C = 20 through 24 and E = 1 through 500
12 if N = Blank or /, C = 20 through 24, and E = 500 through 8000 or C = 24 through 38 and E = 1 through 8000
12 if O = Blank or /, C = 20 through 24, and E = 500 through 8000 or C = 24 through 38 and E = 1 through 8000
15 if N = Blank or /, C = 38 through 180, and E = 1 through 8000
15 if O = Blank or /, C = 38 through 180, and E = 1 through 8000

I hope I explained this good enough as I know it's a lot of data to look at.
Any help will be greatly appreciated!

 

[JDSA84]

SewingFloorProductionLog.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	PALLET NUMBER	WORK ORDER	DIA (in.)	THICK (mm)	LENGTH (ft.)	SHIP BY	AL	IC	OC	IJ	OJ	IS	OS	EX	INS PRT	END	Line
30	471021	875289	48	19.5	366	7/7	10	10	10	-	-	10	10
31	471024	875293	54	21	171	7/7	10	10	10	10	-	10	/
32	471025	875294	54	21	189	7/7	10	10		-	-
33	471026	875295	48	19.5	99	7/7	10	10	10	-	-
41	2317531	875667	24	18	317	7/7	10	10	10	-	-	10	10	10	10
43	470531	874353	15	12	137	7/8	10	10	10	-	-	10	/
45	470531	874355	12	9	175	7/8	10	10	10	-	-	10	/
46	470791	875011	30	13.5	400	7/8	10	10	10	-	-	10	10	10
47	471001	875263	24	10.5	406	7/8	10	10	10	-	-	10	10	10	/
48	471002	875265	24	16.5	205	7/8	10	10	10	-	-	10	10
49	471003	875267	24	12	141	7/8	10	10	10	-	-	10
50	471031	875325	24	10.5	820	7/8	10	10	10	-	-	10	10	10
51	471071	875345	24	10.5	220	7/8	10	10	10	-	-
52	471072	875346	30	13.5	200	7/8	10	10	10	-	-	10	10
53	2316791	875373	18	10.5	1111	7/8	10	10	10	-	-	10	10	10
54	2316792	875374	18	10.5	1277	7/8	10	10	10	-	-	10	10	10	/
55	2316793	875375	18	10.5	902	7/8	10	10	10	-	-	10	10	10
56	2316854	875389	12	6	2700	7/8	10	10	10	-	-
57	2316855	875390	15	7.5	1800	7/8	10	10	10	-	-	10	10	10	10
JobSheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:P	Cell Value	=10	text	NO
J57:K133	Other Type	Color scale		NO
G538:I538,L416:P453,G137:I144,L287:P318,M319:P369,H165:I165,L538:P538,G28:I28,H145:I146,G3:I24,L3:P24,L137:P144,G287:I453,L147:P161,L146:M146,O146:P146,L28:P28,G40:I133,M374:P415,M370:M373,O370:P373,G147:I161,G38:H39,L38:P133,L165:P281,G168:I281	Other Type	Color scale		NO
J539:K541,J2:K24	Other Type	Color scale		NO
J538:K538,J38:K56,J28:K28	Other Type	Color scale		NO
J32:K34	Other Type	Color scale		NO
L32:P34,G32:I34	Other Type	Color scale		NO
J29:K31	Other Type	Color scale		NO
L29:P31,G29:I31	Other Type	Color scale		NO

 

[JDSA84]

Think I've found the best way but can't get a loop to work. The loop would move it through the worksheet Looking for the values from Cell L2:L500, C2:C500, E2:E500 and place the Then in Q2:Q500 if there
Everything I've tried is bringing back a type mismatch.

For r = 2 To 500
If Worksheets("JobSheet").Range("L" & r) = "" Or Worksheets("JobSheet").Range("L" & r) = "/" And Worksheets("JobSheet").Range("C" & r) <= 30 And Worksheets("JobSheet").Range("E" & r) <= 500 Then Worksheets("JobSheet").Range("Q" & r).Value = "4"
Next r
End

 

[JDSA84]

Think I've found the best way but can't get a loop to work. The loop would move it through the worksheet Looking for the values from Cell L2:L500, C2:C500, E2:E500 and place the Then in Q2:Q500 if there
Everything I've tried is bringing back a type mismatch.

For r = 2 To 500
If Worksheets("JobSheet").Range("L" & r) = "" Or Worksheets("JobSheet").Range("L" & r) = "/" And Worksheets("JobSheet").Range("C" & r) <= 30 And Worksheets("JobSheet").Range("E" & r) <= 500 Then Worksheets("JobSheet").Range("Q" & r).Value = "4"
Next r
End

Got this to work by removing formatting on the sheet. Now my issue is it put 4 in the cell if it meets one of the conditions not all.