Colour Coding Cells

Question

Hi there,

I need help!

I am trying to colour code cells that match criteria in a different column.

For example:

When cell in column F = 00/01/1900 AND cell in column I = 00/01/1900 - I want the cell in column O to be "Green" both in colour and say "Green"

When cell in column F = (a date in the past month) AND cell in column I = 00/01/1900 - I want the cell in column O to be "Purple" both in colour and say "Purple"

When cell in column F = (a date later than 6 months prior to current date) AND cell in column I = 00/01/1900 - I want the cell in column O to be "Blue" both in colour and say "Blue"

When cell in column F = (a date in the past month) AND cell in column I = (a date later than 6 months prior to current date) - I want the cell in column O to be "Silver" both in colour and say "Silver"

When cell in column F = (a date in the past month) AND cell in column I = (a date in the past month) - I want the cell in column O to be "Gold" both in colour and say "Gold"

The report that I am creating, needs to be able to update when new data is added on a monthly basis so we can track the progress of data and what changed.

I hope this makes sense and any help would be much appreciated.

Thanks in advance!

E

Answer 1

are F2 and I2 formatted as dates?

Answer 2

Thanks Tom.

It now works - but everything is "gold" even when they shouldn't be.

Sorry!

Emily

Answer 3

Hi,

sorry I wrote it in a hurry and made a couple of typos. Try this:

=IF(AND(F2="00/01/1900",I2="00/01/1900"),"Green",

IF(AND(F2-NOW()<30,I2="00/01/1900"),"Purple",

IF(AND(F2-NOW()>183,I2="00/01/1900"),"Blue",

IF(AND(F2-NOW()<30,I2-NOW()>183),"Silver",

IF(AND(F2-NOW()<30,I2-NOW()<30),"Gold",)))))

Cheers

Tom

Answer 4

Hi Tom,

Thanks for your help! I have input the formula and changed it to 2 rather than 1 as it is row 2. so it looks like this:

=if(and(F2="00/01/1900",I2="00/01/1900"),"Green",

if(and(F2-now()<30,I2="00/01/1900"),"Purple",

if(and(F2-now()>183,I2="00/01/1900"),"Blue",

if(and(F2-now()<30,I2=-now()>183),"Silver",

if(and(F2-now()<30,I2=<30"),"Gold",)))))

and then an error box appeared saying:

"The formula you typed contains an error.

• For information about fixing common formula problems, click Help.
• To get assistance in entering a function, click Function Wizard
• If you are not trying to enter a formula, avoid using and equal sign (=) or minus sign (-), or precede it with a single quotation mark ('). "

What do I need to change?

Thank you

Emily

Answer 5

Hi Emily,

Simplest method is with a formula and then conditional formatting.

Write the following formula in the cell in column O (I'm assuming we're using row 1 for the purposes of this example but change the names in the cell references if not)

=if(and(F1="00/01/1900",I1="00/01/1900"),"Green",

if(and(F1-now()<30,I1="00/01/1900"),"Purple",

if(and(F1-now()>183,I1="00/01/1900"),"Blue",

if(and(F1-now()<30,I1=-now()>183),"Silver",

if(and(F1-now()<30,I1=<30"),"Gold",)))))

Then just highlight column ) and click on "conditional formatting" in the menu and add rules to format the respective colours based on the cell values in those cells i.e. the text "Green" etc...

If you need any help with the conditional formatting just give me a shout and I'll explain further

Cheers

Tom