Arithmetic overflow error converting varchar to data type numeric - Can't find answer that fits

There are two problems here, the first is that just because something passes as ISNUMERIC does not mean you can convert it to a decimal. The following will return 1:

SELECT  ISNUMERIC('1,0');

But this will return an error:

SELECT CAST('1,0' AS DECIMAL(3, 1))

The other, less obvious problem is that you have no control over when SQL Server will perform which operation, so even though you have ISNUMERIC(resultValue) = 1 in the first CTE, there is no guarantee that this will be executed before WHERE CAST(ResultValue AS decimal(3,1)) BETWEEN 7.5 AND 10. So you could still be trying (and failing) to cast non numeric values to a decimal. It works when it is in the SELECT because this is evaluated after the WHERE.

There are two ways around this, the latter is a hack. The preferable way around it is to force the materialisation of the first query by using a temporary table:

CREATE TABLE #ResultsComponent
(
    [RequisitionNumber] VARCHAR(50),
    [ResultsName] VARCHAR(50),
    ResultValue DECIMAL(3, 1)
);
INSERT #ResultsComponent (RequisitionNumber, ResultsName, ResultValue)
SELECT [RequisitionNumber]
      ,[ResultsName]
      ,LTRIM(RTRIM([ResultValue]))
FROM dbo.RESULTS_TEST_COMPONENT
--WHERE TestGroupNumber IN ('T4367', 'T8033')
WHERE SUBSTRING(RequisitionNumber, 2, 4) = '1521'
AND ResultsName = 'HEMOGLOBIN A1C'
AND ISNUMERIC(ResultValue) = 1;

SELECT  RequisitionNumber, ResultsName, ResultValue
FROM    #ResultsComponent
WHERE   ResultValue BETWEEN 7.5 AND 10;

The other way, which is a hack, and not guaranteed to work is to use TOP to force the order of execution, but ensure you choose a value high enough that it doesn't affect the results:

WITH RESULTS_TEST_COMPONENT_CTE(RequisitionNumber, ResultsName, ResultValue)
AS
(
SELECT TOP (100000)
        [RequisitionNumber]
      ,[ResultsName]
      ,LTRIM(RTRIM([ResultValue]))
FROM dbo.RESULTS_TEST_COMPONENT
--WHERE TestGroupNumber IN ('T4367', 'T8033')
WHERE SUBSTRING(RequisitionNumber, 2, 4) = '1521'
AND ResultsName = 'HEMOGLOBIN A1C'
AND ISNUMERIC(ResultValue) = 1
)
SELECT RequisitionNumber
      ,ResultsName
      ,CAST(ResultValue AS decimal(3,1)) AS ResultValue
FROM RESULTS_TEST_COMPONENT_CTE
WHERE CAST(ResultValue AS decimal(3,1)) BETWEEN 7.5 AND 10;

We can recreate the problem fairly simply:

CREATE TABLE #T (ResultValue VARCHAR(100))
INSERT #T (ResultValue)
SELECT  t.ResultValue
FROM    sys.all_objects o
        CROSS APPLY (VALUES (CAST(Name AS VARCHAR(100))), (CAST(object_id AS VARCHAR(100)))) t (ResultValue);

WITH CTE AS
(   SELECT  ResultValue = CAST(ResultValue AS INT)
    FROM    #T
    WHERE   ISNUMERIC(ResultValue) = 1
)
SELECT  *
FROM    CTE 
WHERE   ResultValue BETWEEN 1 AND 10;

And then view the estimated plan (can't get actual plan due to error) and see that both predicates (ISNUMERIC and BETWEEN 1 AND 10) are evaluated at the same time:

By adding top we can change the plan, so that the initial table scan filters for ISNUMERIC and then uses an additional step to ensure BETWEEN 1 AND 10 is applied only to numeric data:

WITH CTE AS
(   SELECT  TOP (1000000) ResultValue = CAST(ResultValue AS INT)
    FROM    #T
    WHERE   ISNUMERIC(ResultValue) = 1
)
SELECT  *
FROM    CTE 
WHERE   ResultValue BETWEEN 1 AND 10;