Mass Conditionally Format a column with 3 rules, based on adjacent cell value

How can i Mass Conditionally Format a Column based on adjacent cell.

I am conditionally formatting a column using 3 rules. which are Between, Greater than, and Less than.

Column E is a specific date, My Conditionally Format is in Column F.

Each Cell in Column F is based on the adjacent date in column E. Both columns are going to have a specific date value entered.

Column F value will stay plain with regular automatic black text when date entered lands within 7 days of adjacent date in column E.
If any other date that is entered other than the specific 7 days will have RED Bold Text with a light red cell fill.

So for example:
Cell F4 is conditionally formatted with 3 Rules based on Cell E4
Cell F5 is conditionally formatted with 3 Rules based on Cell E5
Cell F6 is conditionally formatted with 3 Rules based on Cell E6
and so on....

Is there a way to do this in a Mass Copy and Paste or am I stuck going one cell at a time?

Will the format painter work or is that not doing the trick? Can you send you sheet for review?

I have not tried format painter and will look into it.

i can send a copy of the sheet for review.

Working Log 9 30 13.xlsx

here is the sheet

format painter does not work.

each cell in Column F has to be base on the date adjacent in Column E.

here is your sheet back. I think it now does what you want. I used the format painter, but I first had to change your conditions from absolute to relative references. I hope this gets what you need.

It seems to me that you only have 2 conditions to format, i.e. less than or greater than, and you could combine them into one condition. You can do this in one operation by highlighting all the cells in column F - suppose your data starts with F2, then click on Conditional Formatting | New Rule and choose Use a Formula from the bottom of the list. Then enter this formula in the dialogue box:

=OR($F2<$E2-7,$F2>$E2+7)

Then click the Format button and choose your colour from the Color drop-down, as well as Bold, and on the Fill tab you can choose light red for the background.

When you exit the dialogue box Excel will adjust those cell references to suit the other highlighted cells.

If you need to apply the format to other rows, you can use the Format Painter icon.

Hope this helps.

Pete

Thank you that was pretty fast. What did you change in the conditions can you walk me through this?

Sure. You had your formatting set up as absolute cell references. I went into your condtions and changed all the "$E$#" into "$E#". This allowed me to use the format painter to change the rest of the cells.

Also if you want to reduce the number of conditions you have Pete_UK made an excellent point. Your form should work fine the way you have it, but you could reduce to just 2 conditions. Glad we could help!

Thank You both. This was very helpful. I tried to think of a formula earlier but I keep using "=IF", never even thought of "=OR"