Excel help please. Conditional colour change

Colcoops

I have a spreadsheet with forecast delivery dates for each row. I want to highlight the row's that are within 14 days of the forecast by changing the colour of the text or fill. I would also like to change to a different colour as I get to 7 days of the forecast.

Anyone help?

Sorry wrong board..

alchemista

It's called conditional formatting, but this really doesn't belong on the "grabbit board".

Look in Excel for conditional formatting and you'll find out how.

jon2432

Highlight the cell, right click and select format cells. Then patterns and chose your colour.

iptetbury

Hello Colcoops

This is my first ever post on the MSE forum so hope it is of value...
For the functionality you are after you need to follow the below instructions...

If I can help further then please let me know

Cheers
iptetbury



Assumptions I have made:
1. First row of data that requires the conditional format is row 2 as you are likely to have titles in row 1 which do not require conditional format.
2. The column which details the forecast delivery date is column A.
3. Your workbook has data down to row 99.

Instructions
Highlight row 2 (or first row of data which requires conditional format)
Select "Format" from menu options
Select "Conditional Formatting" from the sub menu option
You should get a pop-up window "Conditional Formatting"
Change default option of "Cell Value Is" to "Formula Is"
Enter in the following formula " =IF(AND($A2<NOW()+14,$A2>NOW()+7), TRUE, FALSE) "
Click on the "Format" button and set the format you desire for rows with delivery less than 2x weeks but more than 1x week.
Click on the "OK" button to return to the "Conditional Formatting" window.
Click on the "Add>>" button to add a second conditional format (for those rows with delivery date less than 1x week)
For Condition 2 again change default option of "Cell Value Is" to "Formula Is"
Enter in the following formula " =IF($A2<NOW()+7, TRUE, FALSE) "
Click on the "Format" button and set the format you desire for rows with delivery less than 1x week.
Click on the "OK" button to return to the "Conditional Formatting" window.
Click on the "OK" button to return to your workbook.
Copy the entire of row 2 (so you have the marching ants)
Highlight all of the other rows of data you would like the conditional formatting applied (for example row 3 through to row 99).
Select "Edit" from menu options.
Select "Paste Special" from the sub menu option.
Select "Formats" option within the "Paste Special pop-up window.
Click on the "OK" button to return to your workbook.
:j

cwoodham

This link might help.

http://www.mrexcel.com/tip054.shtml

Colcoops

iptetbury wrote:

Hello Colcoops

This is my first ever post on the MSE forum so hope it is of value...
For the functionality you are after you need to follow the below instructions...

If I can help further then please let me know

Cheers
iptetbury


:j

Brilliant 1st post. Its good to have you on board. I'll try it out at work.
Thanks
Coops

johnmc

As you get more into Excel I suggest that you sign up *FREE* to;
http://www.utteraccess.com/

You can still look at the answers without registering (which is essential to place a question).