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]

@JestonH 

Thank you. Here is a screenshot of the rules for column G:

There are three problems here:

1. The rules apply to G1:G51, but the formulas refer to the cell in row 2.
2. The formula refers to $E$2. The $ sign before the row number make this number absolute (not-changing).
3. The text values Curio and Orsino have not been enclosed in double quotes ".

To correct this, change the formulas to

=$E1="Curio"

and

=$E1="Orsino"

See the attached workbook.

[#3]

Thank you for both the example and the clarifying explanation!

[#4]

How do I apply this to all cells? I can only get it to change the cell colours in the first row @Hans Vogelaar 

[#5]

@Laura Backshell 

Select all the cells that you want to format conditionally before you create the rule.

[#6]

I can't be doing it properly

@Hans Vogelaar 

[#7]

@Laura Backshell 

This rule will be applied to A2:B3, and the formula refers to C45. Is that really what you intended?

[#8]

no, ive done it before ages ago but I can't remember how to apply it to all cells required.

I would like the cells in column A/B3 to A/B64 to highlight green if the text in column C says Active.

[#9]

@Laura Backshell 

Rule formula shall be applied to to top left cell of the range to which you apply the rule. CF iterates the range applying the formula to each cell of the range. Formula applied to each next cell depends on do you use relative (like A1) or absolute (like $A$1) reference, or their combination.

[#10]

@Laura Backshell 

Select A3:B64 and make sure that A3 or B3 is the active cell in the selection.

Select Conditional Formatting > Manage Rules...

Delete any rules that are not working correctly.

Then create a new rule of type 'Use a formula to determine which cells to format' with formula

=$C3="Active"

3 is the row number of the active cell; there is no $ before the row number 3 because we want Excel to change it dynamically for rows 4 to 64.

[#11]

Can you make the formula relative ?

I'm looking to indicates if the values in a table are rising , or dropping by color as the number are sometimes very close.
If the Value is Larger or Smaller than the value to the Right...
A        B      C    D     E
123 125 122 130 145
I'd like the cell to Turn Red if values are rising - Green if dropping relative to the previous day. ( Cell to the Right )
Of course there are multiple Rows :)

[#12]

@MrKasifs 

Let's say your data are in A2:E100 (or further down and/or to the right).

Select B2:E100 (i.e.the entire range except the cells in column A).

B2 (the top left cell in the selection) should be the active cell.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula

=A2

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

Repeat these steps, but with 'less than' and green as fill color.

[#13]

Hello,
Very close - but It's my fault for not being clear in my request / explanation.
Every column is a different day - and I want to indicate from day to day did things get *Better* or Worse :)
Was Saturday better or worse than Friday, Friday than Thursday
The comparison is between two columns. A:B / B:C / C:D / D:E etc...

Thank you for taking the time.

2023-09-27 2023-09-26 2023-09-25
2630 2621 2620
5874 5867 5868
2126 2122 2124
4357 4354 4355
918 920 920
8850 8825 8818
7062 7055 7062

[#14]

@MrKasifs 

Yes, that's how I interpreted your question. The conditional formatting rule that I proposed should do that.