excel vba moving the offset to start of the range if exist (functional way of writing)

Question

Goal is to identify the start of the region value and it will be used in offset.

1. Input will be dynamic
2. Row length is not fixed.
3. Only criteria is region begin and end, but it may exist or not (see the sample input below)

Criteria	Values
Sample Input	1 to 10
Region begin	4
Region end	7

I have written the following code in procedural way, is there a better functional way of writing the following code for better management and readability?

Sub findBorder()
    Dim myrange As Range
    Dim rCell As Range
    
    ' (1 2 3 4 5 6 7 8 9 10)
    Set myrange = Range("D1:D10")
    Dim mark_region_begin As Integer
    Dim mark_region_end As Integer
    
    Dim mark_region_after_begin As Integer
    Dim mark_region_before_end As Integer
    
    Range("F1").Value = ""
    Range("F2").Value = ""
    Range("F3").Value = ""
    
    ' Find in bewteen and including 4 & 7
    For Each rCell In myrange:
        If rCell.Value = 4 Then
            mark_region_begin = rCell.Row
        ElseIf rCell.Value > 4 And rCell.Value < 7 Then
            If mark_region_after_begin = 0 Then
                mark_region_after_begin = rCell.Row
            End If
            mark_region_end = rCell.Row
        ElseIf rCell.Value = 7 Then
            If mark_region_begin = 0 Then
                mark_region_begin = rCell.Row
            End If
            mark_region_end = rCell.Row
        End If
    Next rCell
    
    If mark_region_begin = 0 Then
        mark_region_begin = mark_region_after_begin
    End If
    
    Range("F1").Value = mark_region_begin
    Range("F2").Value = mark_region_after_begin
    Range("F3").Value = mark_region_end
    
End Sub

Sample input:

In the following sample input, The column E is sorted, and the data in the range between -0.15 to +0.15 will be selected.

A	B	C	D	E
4190.65	4227.6	4332.717	19.79	-7.05
721.05	734.65	741.9	12.25	-6.80
4273.05	4303.5	4420.3	14.89	-6.60
1132.25	1150.3	1180.75	2.56	-6.54
1134.55	1140.6	1168.317	22.12	-6.47
4015.5	4030.4	4135.3	13.94	-6.34
597	608.6	617.8	4.19	-5.82
1355	1360.95	1385.317	2.95	-5.60
2104.1	2116.55	2158.483	6.93	-5.49
808.9	816.45	830.8167	1.54	-5.39
367.2	370	377.0667	7.87	-5.29
347.75	349.65	357.2167	3.28	-5.18
7045.1	7178.65	7178.633	11.76	-5.03
515.9	521.95	529.3	4.05	-4.91
2002.4	2026.3	2071.267	4.24	-4.88
100.5	101.2	102.7	2.36	-4.78
116.95	117.7	119.4833	17.83	-4.75
460.85	463.1	468.8	2.21	-4.69
2326.1	2343.6	2379.25	8.58	-4.49
193.8	196.25	198.85	0.71	-4.49
2277.7	2290.4	2327.683	6.18	-4.39
711.25	714.5	726.75	3.32	-4.34
14285.7	14354.5	14580.02	5.18	-4.33
6310.1	6365.15	6456.383	4.51	-4.31
119.2	120.65	122.6167	12.48	-4.31
80.8	81.3	82.96667	1.11	-4.29
1537.25	1549.45	1585.133	6.86	-4.23
778.05	785.75	797.9333	7.22	-4.18
459.85	461.55	468.8	-0.05	-4.15
2406.15	2413.75	2457.283	2.79	-4.15
243	244.7	249.05	0.55	-4.14
742.6	751.3	759.9167	7.98	-4.13
310.75	312.85	317.8167	0.81	-4.13
108.05	108.7	110.8167	5.24	-4.10
912.7	922	932.5667	3.01	-4.06
626.1	629.55	637.3666	8.14	-3.89
3767.4	3800.95	3871.116	11.39	-3.88
4333.15	4348.5	4406.033	6.91	-3.88
1444.55	1449.9	1469.15	4.14	-3.83
2303.15	2324.6	2347.9	8.44	-3.77
1036.05	1043.35	1058.967	1.72	-3.77
685.9	688.6	696.3499	1.75	-3.74
2242.95	2263.4	2292.116	1.84	-3.73
1492.35	1500.3	1527.467	10.11	-3.68
2436.5	2488.7	2506.85	6.64	-3.64
1795.85	1804.5	1821.783	2.37	-3.62
972.2	978.9	994.8667	2.7	-3.61
2686	2696.85	2728.3	4.57	-3.59
1620.15	1627.35	1649.75	2.06	-3.55
145.5	146.45	150.0667	2.77	-3.46
419.45	421.6	427.1	4.48	-3.31
196	203.8	204.1333	3.73	-3.29
266.5	268.25	271.6167	0.79	-3.28
3978.4	4003.5	4041.25	3.48	-3.27
338.5	339.55	343.5	1.63	-3.26
1899.3	1909.65	1934.583	0.23	-3.21
534.15	539.1	547.9167	1.77	-3.20
221.25	223.1	227.0167	1.07	-3.10
3371	3412.65	3443.45	2.25	-3.09
3083.1	3102.8	3128.1	5.42	-3.07
25129.5	25237.55	25722.35	3.99	-3.06
2687	2703.05	2728.333	2.2	-3.04
113	114.1	115.4833	0.59	-3.03
3484.1	3498.85	3548.533	1.29	-3.00
694.55	698.25	706.7333	3.17	-2.99
1962.45	1978.05	2000.167	4.74	-2.99
1339.55	1346.7	1359.667	3.89	-2.98
229.2	231.8	234.0333	1.22	-2.95
624.2	626.9	634.05	3.33	-2.93
7372.05	7416.9	7516.267	0.64	-2.92
367.7	369.35	373.6167	1.98	-2.88
109.45	109.95	111.1833	1.79	-2.87
1252.2	1258.3	1267.833	8.8	-2.81
392.65	395.65	399.9333	4.08	-2.79
323.75	326.95	330.4167	1.12	-2.77
8446.35	8582.25	8631.017	1.04	-2.76
706.25	709.95	718.1667	1.1	-2.76
1320.6	1331.85	1349.333	13.81	-2.76
476.6	479.3	484.4667	4.82	-2.75
761.3	767.35	779.0334	3.47	-2.75
2152.05	2173.95	2193.667	0.9	-2.75
716.3	718.95	727.75	0.29	-2.72
110.8	111.4	112.9333	2.11	-2.71
2405.95	2417.15	2451.2	2.72	-2.67
69892.8	70396.05	71025.88	3.6	-2.63
16210	16280.9	16376.4	13.1	-2.59
6507.75	6526.7	6585.333	3.54	-2.56
2197	2210.75	2229.35	5.88	-2.55
718.25	722.6	727.2333	1.12	-2.54
258.15	259.85	263.1833	2.28	-2.54
710.3	713.65	719.9333	3.07	-2.52
16600	16722.15	16797.17	-0.74	-2.51
366.1	369.55	373.1	5.47	-2.47
825	827.85	837.8167	-0.17	-2.47
113.95	114.3	115.45	3.82	-2.43
517.75	521.65	526.95	-0.04	-2.42
93.95	94.95	95.9	3.31	-2.42
35145.95	35308.15	35598.03	14.74	-2.38
123.2	123.7	125.0167	2.39	-2.37
894	899.85	906.1833	5.82	-2.36
3194.8	3203	3231.267	4.6	-2.35
156.4	157.05	158.6333	-0.87	-2.33
14110	14168.85	14268.67	6.25	-2.33
1631.9	1640.3	1653.017	3.86	-2.32
304	305.85	308.5333	-1.25	-2.32
896.2	901.6	907.6667	7.99	-2.31
2742	2791.2	2808.5	3.55	-2.30
433.75	438.5	443.45	-0.87	-2.24
572.85	578.9	583.2167	0.93	-2.21
2466.35	2493.85	2511.3	6.7	-2.20
2199.85	2204.25	2223.95	2.93	-2.20
2008.9	2022.05	2035.367	2.78	-2.19
442.2	448	451.7333	-1.46	-2.19
199.4	200.05	202.0333	4.69	-2.18
129.75	130.6	131.8667	4.3	-2.17
2666.05	2691.8	2711.833	3.22	-2.16
950.45	958.45	964.05	3.02	-2.15
16643.2	16702.5	16827.82	25.56	-2.15
254.85	257.85	260.7	-1.22	-2.15
724	727.05	733.55	2.57	-2.14
982	986	997.45	9.45	-2.12
750.2	757.7	764.95	0.06	-2.11
421.25	424.05	428.95	1.21	-2.11
3815.25	3842.65	3881.384	-1.83	-2.07
603.1	609.25	611.9667	0.52	-2.05
255.2	258.35	260.4667	2.8	-2.03
540.6	543.45	548.0167	2.05	-2.03
802.5	805.9	812.9333	4.24	-2.02
2420	2435.65	2454.883	4.32	-1.99
18000	18106.2	18219.4	4.03	-1.98
276.45	279	281.5334	2.91	-1.94
2007.65	2018.05	2031.683	3.32	-1.93
507.25	512.55	515.4167	2.74	-1.90
723	727.75	734.4	1.88	-1.88
1040.45	1049.45	1056.6	3.19	-1.85
566.2	569.4	572.4666	2.48	-1.84
2275	2300.25	2313.417	-0.77	-1.82
54.8	55.25	55.93333	0.2	-1.77
366	367.75	370.5333	1.82	-1.74
39067.45	39122.3	39462.92	5.62	-1.73
1776.35	1781.6	1793.317	3.87	-1.69
1205.25	1217.45	1224.033	3.62	-1.67
1248.4	1254.4	1265.267	0.87	-1.67
1595.25	1598.65	1611.25	4.68	-1.60
131.6	132.2	133.5333	-0.9	-1.56
1559.9	1566.7	1576.75	-2.17	-1.56
1258.8	1265.05	1282.95	7.18	-1.47
1010.6	1015	1022.533	0.25	-1.38
3471.25	3483.45	3507.783	3.35	-1.29
198.6	200.2	201.8667	5.44	-1.28
1620.7	1629.5	1640.817	2.78	-1.26
4034.2	4053.5	4088.75	-1.13	-1.25
178.05	179.95	182.95	12.54	-1.21
258.2	259.3	260.9	8.69	-1.18
834.4	838.8	842.7334	-2.55	-1.10
360.55	363.8	365.4666	0.93	-1.09
529.5	530.9	535.1334	4.22	-1.05
4102.2	4125.15	4159.05	0.64	-1.02
243.75	245.9	248.15	0.9	-0.90
93.9	94.55	95.56667	0.47	-0.84
668.5	674.3	680.2	-0.89	-0.81
2086.2	2099.35	2110.583	8.33	-0.81
338.85	343.95	344.25	2.12	-0.75
818	825.95	831.2834	3.78	-0.73
490.75	493.8	496.7	0.7	-0.71
898.2	911	912.0334	1.71	-0.68
823.1	829.9	834.7834	4.77	-0.62
45142.85	45377.95	45544.61	5.69	-0.61
1270.9	1279.05	1281.983	0.57	-0.53
516.25	519.8	524.5667	1.34	-0.52
3914.1	3943.4	3963.833	3.67	-0.52
125.6	126.4	127.0667	0.27	-0.51
764.15	768.5	771.2166	1.87	-0.47
3451.15	3480.1	3497.316	0.55	-0.30
1067.5	1080.7	1081.767	1.77	-0.28
638.2	642.85	644.65	1.74	-0.26
1536.45	1542.05	1550.75	2.02	-0.13
401	406.15	407.6167	2.21	-0.09
2267.2	2296.15	2313.85	5.95	-0.03
270.7	272.3	273.4833	0.48	-0.02
156.5	158.1	158.4167	0.87	0.00
186.9	187.9	188.65	0.89	0.03
1572.2	1585.25	1590.95	0.02	0.15
1206.15	1214.65	1219.1	1.75	0.23
1770.5	1783.5	1794.05	6.99	0.24
116.35	117.35	118.95	4.16	0.25
4690	4744.95	4754.633	0.35	0.29
1031	1039.4	1042.133	3.65	0.41
151.3	152.5	153.5667	1.62	0.63
157.6	158.8	159.5	2.88	0.92
486.2	497.4	495.3333	-9.39	1.67
233.65	237.9	237.0833	4.66	2.65
208.4	213.8	212.3833	1.64	2.66
158.45	162.75	161.5667	-1.41	3.69
3344.9	3395.5	3447.267	11.16	3.71
168.4	176.5	174.7667	19.57	5.77

Answer 1

Not directly answer to your question. But if I understand you correctly,

Sub test()
Dim rg As Range
Dim minVal As Integer
Dim maxVal As Integer
Dim oBegin As String
Dim oAfterBegin As String
Dim oEnd As String

With ActiveSheet
    Set rg = .Range("E1:E20")
    minVal = .Evaluate("min(if(" & rg.Address & ">=3.5," & rg.Address & "))")
    maxVal = .Evaluate("max(if(" & rg.Address & "<=11.5," & rg.Address & "))")

    oBegin = rg.Find(minVal).Address
    oAfterBegin = Range(oBegin).Offset(1, 0).Address
    oEnd = rg.Find(maxVal).Address
End With

End Sub

The code assumed that the data to be test is in cell E1 to E20 and already sorted.
Example data in column E from row 1 to 20 is a number from 1 to 20 on each row.
The goal is to find the cell address which contain :

1. the nearest bigger or equal number of 3.5 ---> this will be the row which contains number 4 in column E.
2. the nearest less or equal number of 11.5 ---> this will be the row which contains number 11 in column E.

To get point-1 result, the code using MIN function then keep it in minVal variable
To get point-2 result, the code using MAX function then keep it in maxVal variable

Then the code find the value of minVal/maxVal and get the address of the found cell.

So,
the oBegin variable value will be cell E4 ($E$4 which contains a number : 4)
the oEnd variable value will be cell E11 ($E$11 which contains a number : 11)
the oAfterBegin variable value will be $E$5