You don't need IMPORTRANGE. Also, you can't have a formula that checks only for color (you would need a script or addon for that). However, you mention that you already have conditional formatting rules in place that already do their job to turn cells red so you can gain an advantage of it and use the same rules in your Main sheet (but not as CF) to get data you need.
- paste in B2 cell:
=QUERY({
Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G;
Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I;
Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F},
"where Col1 < date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'
and Col1 > date '"&TEXT(TODAY()-99999, "yyyy-mm-dd")&"'", 0)
- paste in C2 cell:
=QUERY({
Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G;
Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I;
Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F},
"where Col1 > date '"&TEXT(TODAY(), "yyyy-mm-dd")&"'
and Col1 < date '"&TEXT(TODAY()+30, "yyyy-mm-dd")&"'", 0)
- paste in D2 cell:
=QUERY({
Cosmic_Fog!D7:D; Cosmic_Fog!E7:E; Cosmic_Fog!F7:F; Cosmic_Fog!G7:G;
Charlie_Noble!D7:D; Charlie_Noble!E7:E; Charlie_Noble!F7:F; Charlie_Noble!G7:G; Charlie_Noble!H7:H; Charlie_Noble!I7:I;
Cloudmouth_Vapors!D7:D; Cloudmouth_Vapors!E7:E; Cloudmouth_Vapors!F7:F},
"where Col1 > date '"&TEXT(TODAY()+29, "yyyy-mm-dd")&"'
and Col1 < date '"&TEXT(TODAY()+61, "yyyy-mm-dd")&"'", 0)
- if you want to get rid of duplicates wrap each query into
UNIQUE