How do I make excel change the colour of a cell depending on a different cells date?

[Daniel Cook]

Hi,

How do I make excel change the colour of a cell depending on a different cells date?

Turn A2 red if - E2 cell is smaller than todays date

Turn A2 yellow with red outline if - E2 cell is equal to todays date

Turn A2 clear if - E2 cell is bigger than the current date

[#2]

Hi Daniel,

In your screenshot there is no any date in E2. But in general

Stay on A2, Home->Conditional formatting->Manage rules->New ruke; use for the ruke

=$E2<=TODAY()

Format, apply any formatting you want. After that apply the rule to entire range (e.g. $A$2:$A$10), or keep only A2.

Repeat for each color /condition you'd like to have

[#3]

I don't understand, Sorry

[#4]

Hello,

column E does not have dates. Column F does, and so does column H. In the following I assume you want to use the date in column F. Change the formula to use H if you want to. 

1. Select cell A2
2. click Conditional Formatting on the Home ribbon
3. click New Rule
4. click Use a formula to determine which cells to format
5. click into the formula box and enter the formula
   =$F2<Today()
   
6. click the Format button and select a red color
7. close all dialogs

Repeat the steps and use the formula

=$F2=Today()

and select a yellow fill and red border.

Next, select cell A2 again, click the Format Painter in the home ribbon, then select all other cells below cell A2.

Does that help?

[#5]

@Ingeborg Hawighorst 

Do you happen to know how to change the cell colour based on an other cell colour? 

IE we have a shipping sheet with user name, address, contact number, items in box, box size, tracking number etc and the tracking number cell column is highlighted a colour based on its delivery (we change it to green once delivered) but we'd like the first cell in the row (or whole row) to also change green showing that whole line is complete. 

I cannot find anything to support this 

[#6]

@Ingeborg Hawighorst 

i would like to be able to move the conditions to different cells. 

basically id like to condition all the cells based on their own respective value without having to cell by cell

[#7]

@Daniel Cook Try this simple trick

:

howhide.com/simple-trick-to-make-excel-change-colour-cell-depending-on-different-cells-date

[#8]

How can I make this formula work using specific words instead of a date?@Ingeborg Hawighorst 

[#9 · Accepted solution]

@tmcmahon71 

Let's say you want to highlight a row if column F contains "Closed".

Use the formula

=$F2="Closed"

If you want something different, please provide more detailed information.

[#10]

YES! That is exactly what I was looking to do. Many many thanks Hans. I so appreciate your help.

[#11]

@HansVogelaar if I want to change a cell color based on a collumn in a different sheet containing the number 221 how do I do that?

So for instance I need my cell to turn green if any cell in column C on the second sheet titled testdone contains the number 221

[#12]

@Staceyaa 

Create a rule of type 'Use a formula to determine which cells to format' with the formula

=ISNUMBER(MATCH(221,testdone!$C:$C,0))

[#13]

@HansVogelaar 

Your answer help me

If cell A8 contain a "text" and D8 contain a text that can be "closed" or "Open"

And i want to change the color of A8 based on D8 text, that is clear to me by using your used fourmla

My inquirry, but if i want to repeat above fourmla for each raw such as

A9 based on D9

A10 based on D10

A11 based on D11

and roll gose on 

[#14]

@SattamSaeidi 

Select A8:A100 (or as far down as you want).

A8 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=D8="Closed"

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=D8="Open"

and another color.

[#15]

@HansVogelaar 

Thank you very much 

thats help, really appreciated 

[#16]

@HansVogelaar 

Hey! This is all great info!

I am trying to make a visual to do list and I was wondering how to make a cell turn a different colour based on if a check box is checked off. For example, in column A I have various to do list items written, column B has check boxes and column 3 I am wanting to make the box turn green when the check box is checked off.

Any help is appreciated! Thanks!

[#17]

@MDuns 

Do the check boxes have a linked cell? If so, which cells do you use for that?

[#18]

@HansVogelaar I tried this approach, but the entire column of cells is still based on D8 instead of the cell in the corresponding row in A. (A10 should be based on D10, A21 on D21, etc...) 

[#19]

@JestonH 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#20]

I don't see how to attach a file, so here's a Dropbox link for a sample.
https://www.dropbox.com/s/dug53y5lufw5wqu/Dialog.xlsx?dl=0
I was trying to have each cell in column G format based on the content of the cell of the corresponding row in column E.