... I need the task to highlight in
red if the suspense date has already passed....
If the task is
due between todays date and 6 weeks out, I need it to highlight yellow.
If the task is due more than 6 weeks out, I need it to highlight Green.

Also when in puting a formula, is there a way that the formula can
automatically change for that row number

Here's one way with Excel 2003.

First, highlight all the date cells of interest in green. Then add conditional formatting for the other two colors.

Specifically, select B7 and use:
Format Conditional Formatting

For Condition 1 use
Formula Is
=AND(B70,B7<TODAY())
and Format with red highlight.

For Condition 2 use
Formula Is
=AND(B70,B7<TODAY()+42)
and Format with yellow highlight.

(Testing B70 is to prevent empty cells from becoming red.)

To apply the conditional formatting to other cells, use the format painter button. It's the button that looks like a paint brush. The conditional formulas adjust for the row and column where the format is painted.