- custom formula for green:
=COUNTIFS(A1; "DCC220"; B1; ">"&C1 ; B1; "<"&D1)
- and for red:
=OR(COUNTIFS(A1; "DCC220"; B1; "<"&C1); COUNTIFS(A1; "DCC220"; B1; ">"&D1))
bonus knowledge: to include C1 and D1 in the range as well, you can add equal sign like this:
- custom formula for green:
=COUNTIFS(A1; "DCC220"; B1; ">="&C1 ; B1; "<="&D1)
FINAL PRODUCT OF OP'S QUESTION:

CUSTOM FORMULA FOR GREEN APPLIED IN E1 FOR RANGE E:E
=ArrayFormula(OR(COUNTIFS(B1, INDIRECT("Index!$A$2"), E1, ">="&INDIRECT("Index!$B$2"), E1, "<="&INDIRECT("Index!$C$2")),
COUNTIFS(B1, INDIRECT("Index!$A$3"), E1, ">="&INDIRECT("Index!$B$3"), E1, "<="&INDIRECT("Index!$C$3")),
COUNTIFS(B1, INDIRECT("Index!$A$4"), E1, ">="&INDIRECT("Index!$B$4"), E1, "<="&INDIRECT("Index!$C$4")),
COUNTIFS(B1, INDIRECT("Index!$A$5"), E1, ">="&INDIRECT("Index!$B$5"), E1, "<="&INDIRECT("Index!$C$5")),
COUNTIFS(B1, INDIRECT("Index!$A$6"), E1, ">="&INDIRECT("Index!$B$6"), E1, "<="&INDIRECT("Index!$C$6"))))
CUSTOM FORMULA FOR RED APPLIED IN E1 FOR RANGE E:E
=ArrayFormula(OR(OR(COUNTIFS(B1, INDIRECT("Index!$A$2"), E1, "<"&INDIRECT("Index!$B$2")), COUNTIFS(B1, INDIRECT("Index!$A$2"), E1, ">"&INDIRECT("Index!$C$2")),
OR(COUNTIFS(B1, INDIRECT("Index!$A$3"), E1, "<"&INDIRECT("Index!$B$3")), COUNTIFS(B1, INDIRECT("Index!$A$3"), E1, ">"&INDIRECT("Index!$C$3")),
OR(COUNTIFS(B1, INDIRECT("Index!$A$4"), E1, "<"&INDIRECT("Index!$B$4")), COUNTIFS(B1, INDIRECT("Index!$A$4"), E1, ">"&INDIRECT("Index!$C$4")),
OR(COUNTIFS(B1, INDIRECT("Index!$A$5"), E1, "<"&INDIRECT("Index!$B$5")), COUNTIFS(B1, INDIRECT("Index!$A$5"), E1, ">"&INDIRECT("Index!$C$5")),
OR(COUNTIFS(B1, INDIRECT("Index!$A$6"), E1, "<"&INDIRECT("Index!$B$6")), COUNTIFS(B1, INDIRECT("Index!$A$6"), E1, ">"&INDIRECT("Index!$C$6")))))))))