Excel Conditional Formatting 3-Color Scale Formula

Question

Hello, 

I hope someone will be able to help me:

I am using Office 365. I have a set of dates in one column, say C and another set of dates in D. The dates in D are always lower (earlier) than the corresponding dates in C. I want to apply three-color grading to dates in D based on the difference between the corresponding date in C. For example, if C4-D4 is less than a week (7), D4 should be green. If C2-D2 is between 1-2 weeks (7<C2-D2<14), D2 should be amber. If C8-D8 is more than four weeks (>28), D8 should be red. Right now, I have three separate rules for each such situation - hence I have three distinct colors and not gradient colors. I am just wondering if there is a way to optimize this?

I am sorry if this is a repetitive question. I tried to search for similar questions but could not find the right one. If there are similar posts, could you please re-direct me?

EDIT: Please see further explanation in reply to a post below

Answer 1

Hi Thenga,

My name is Eric, I have read whole thread carefully and I want to share with you my idea.

Because in column D, we need do conditional formatting base on a formula, Based on formula is more complex then base on a value in the cell for Excel, at present, We can't set three color base on formula at once but set them one by one. 

I know you want to optimize the calculation, because 3-color format style only judge by checking the list one time while the three formulas will check the list three times. A s far as I known, it won't cause delay unless you have a huge database, I always assign more than ten formula format in one sheet and the function works well.

Additional, it is possible to use VBA to achieve our goal, because we can link the color of different cellsby the Macros (for example link C1 to A1):

Private Sub Worksheet_SelectionChange(``ByVal Target ``As Range)

    ``Me``.Range(``"C1"``).Interior.Color = ``Me``.Range(``"A1"``).Interior.Color

End``Sub

If you are interest in it, you can post a new thread in Programming category to get further help about linking color of two different columns.

Regards,

Eric

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Answer 2

Hi Thenga,

If you need further help, you can post your latest condition.

Regards,

Eric

Answer 3

Hi Eric, 

Thank you for your comments. I have dropped my desire to get a formula for the three-color style *sigh*

I tried the VBA route to link colors (thank you!). However, when I link the color of a cell which is colored based on conditional formatting, the pasted color is always White. The method works if I link a cell which is colored manually. Not sure how to work around this. If you could shed some light into this, that would be great!

Thank you. 

Kind Regards, 

thenga