Changing a cell Colour

[petebrewster]

Hoping someone can help, ive been bashing my head for an hour and cant work this out.

I'm trying to create a rule / situation where the color of a cell changes when a certain period of time / date passes.

I have 3 columns, first column will have the date of when the inspection was last carried out, the next column will be when the inspection is next due. Generally i have inspections that are a year apart, although I do have some that require re-inspection after 6 months, 14 months 5 years, but i'm guessing the rule is the same.

I would like the 'due date' cell to change color, twice, the first to yellow, when we are 1 month away from the reinspection date, the second color change will be to red if the reinspection date passes, but the date remains visible in the cell.

Any assistance, or pointing to an idiots guide would be great.

Thanks in advance, Pete

 

[JamesCanale]

Conditional Formatting. I used 30 days for a month.

MrExcelPlayground7.xlsx
	B
1	3/15/2022
2	2/15/2022
3	4/15/2022
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B3	Expression	=TODAY()-B1>0	text	NO
B1:B3	Expression	=TODAY()-B1+30>0	text	NO

 

[mumps]

You can use conditional formatting. Assuming the reinspection dates are in column B, select the date range in column B to highlight it. Then use this formula in the conditional formatting for yellow:
=AND(B2>=TODAY()-30,B2<=TODAY())

and this formula for red:
=B2>TODAY()

 

[petebrewster]

clicking conditional formatting gives me lots of options, ???, sorry, assume i'm completely stupid and talk me through step by step, that would be amazing

 

[mumps]

Select the date range to highlight it. In the Home menu tab, click on Conditional Formatting...New Rule...Use a formula to determine which cell to format, copy/paste the formula in the box "Format values where...". Then click the "Format" button and select the fill color. Repeat for next colour.

 

[petebrewster]

Select the date range to highlight it. In the Home menu tab, click on Conditional Formatting...New Rule...Use a formula to determine which cell to format, copy/paste the formula in the box "Format values where...". Then click the "Format" button and select the fill color. Repeat for next colour.

select date range?, you mean select the cell....,.

 

[mumps]

All the reinspection dates.

 

[petebrewster]

All the reinspection dates.

thanks, but i'm not getting it

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[petebrewster]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

ok, here is the link to the file.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The cells may be populated with other formula, i thought i could use it, but its just got me confused.