How do I make cells turn a certain colour when an expiry date has been reached eg all cells to turn red on 1 July

Hi Priva,

Thank you for your post, welcome to the forum, in reply to your question:

First highlight the range of cells that you want to apply the formatting to. For example, select all of column A since we don't know how many rows will have expiration date values.
Next, under the Format menu, select Conditional Formatting.
When the Conditional Formatting window appears, select "Formula Is" in the drop down. Then enter the following formula:
=ISBLANK(A1)=TRUE
What this formula means is that if any cells in column A are blank, do not apply any formatting. The value of A1 is put in the IsBlank function since this is the first value in the range of cells that you've selected. Since the formula uses relative referencing each value in column A will be evaluated individually.
Next, click on the Add button to create a second condition.
In Condition 2, select "Cell Value Is" in the first drop down and "less than"/”greater than” (dependant where your target date is [future or past]) in the second drop down. Then enter the following formula:
=NOW() (and dependent on whether you are using either the less than or greater than enter the number of days between now and 1st July (ie +30 for in 30 days time) so your entry would be =NOW()+30
Next, click on the Format button for Condition 2.
When the Format Cells window appears, select the Font tab. Then select the colour that you'd like to see the expired dates displayed in. In your example, select red. Then click on the OK button.
Return to the Conditional Formatting window.
Click on the OK button again.
Now when you return to the spreadsheet, the conditional formatting will be applied.

I hope that has solved your problem, if so please click the resolved link, regards Pete