Google Sheets – Conditional Formatting Based on Proximity to Upcoming Date

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:

Format, Conditional formatting..., Custom formula is:

=match(A1,indirect("Roster"&"!D:D"),0)>0  

select yellow (Range: should be A:A) and Save rules.

For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:

=match(A1,indirect("Roster"&"!E:E"),0)>0  

and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).

1. Select the range to which the rule should apply: in my example it is G1:G8.
2. Enter the custom formula criterion for red color: =G1<TODAY(). Note that this says "G1" because the formula is stated as it applies to the upper left corner of the range. It will be automatically adjusted for other cells in the range according to the usual rules for relative references.
3. Click "add another rule", enter the formula for yellow, e.g., =G1<TODAY()+90
4. Click "add another rule", enter the formula for green, e.g., =G1>=TODAY()+90

If several rules apply, those listed later override the earlier ones.

The result: