Calculate days to expiry

Fake data above. I'm trying to figure out how to populate the "Expiring in # Days" cells at the top, using the "Expiry Date" cells at the bottom and an auto-generated "today's date". I need the number of accounts expiring to pop up in the "Expiring in # Days" cells (above), and ideally, if it's 7 days or less till expiry, the specific account's Expiry Date cell (below) would turn red; if 8–14 days till expiry, the cell would turn yellow; if 15–30 days till expiry, the cell would turn orange.

Is it possible?