Formula to change colour of a cell based on info in two diffrent cells.

[Charli07]

Hi Guys, Are you able to help me with the formula for the following.
jones start date 13th of April 2019.
Smith terminate date 29th may 2019.

I need a formula to change colour based on information in two cells In excel

All cells prior to 13th April for Jones and all cells after 29th may 2019 for Jones.

i don't think i am explaining this fully. i Cant attached a copy of the excel spread sheet to is post.

[Aardigspook]

You can do this with Conditional Formatting.
1. Select your range (the range you want to format).
2. On the Home tab of the ribbon, click 'Conditional Formatting', then 'New Rule', then 'Use a formula to determine which cells to format'.
3. In the formula box, enter one of the formulae detailed below.
4. Click 'Format' then choose the colour you want from the 'Fill' tab.
5. Click OK twice to get back to the worksheet.

What formulae you want depends on how your sheet is structured. The generic structure will be something one of these for Jones:

Please Login or Register  to view this content.

For Smith, replace the < sign with > (as well as changing Jones to Smith and changing the date if using the second one, obviously).

Attached is a file showing the first type of formula working, with Names and Start/Terminate dates in columns A-C, then a list of names and dates in columns E and F. The formulae used in the CF are shown in columns G (for Jones) and H (for Smith) - these are just for reference, they're not actually needed, as they're in the CF.
Note the $ signs used for the cell references. For example $E2 makes sure that the formatting always looks at column E to match 'Jones' or 'Smith', and the $A$2 always refers to Jones' start date.

Your sheet is bound to be structured differently, but hopefully you can adapt this.

[Charli07]

Thanks Aardigspook,

that is not what I meant.

I have a work book which will explain better. however I am not able to attach that file.

[Melvosh]

You can use the following conditional formatting formula. Please note that you would need to recreate it for each sheet, as I don't think there's a way to get conditional formatting formulas to apply to more than one sheet:

Please Login or Register  to view this content.

In the Applies to box, select $D$4:$AG$5. You'll obviously need to adjust your ranges to fit the actual data. Does this provide the results you need?