Aug 24 2017 08:57 AM - last edited on Nov 09 2023 11:09 AM by
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
Aug 24 2017 10:18 AM
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
Aug 24 2017 01:27 PM
Aug 24 2017 08:56 PM - edited Aug 24 2017 08:58 PM
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.
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?
Oct 21 2021 01:30 PM
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
Sep 06 2022 08:46 AM
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
Nov 20 2022 07:08 PM
How can I make this formula work using specific words instead of a date?@Ingeborg Hawighorst
Nov 21 2022 03:34 AM
SolutionLet'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.
Nov 21 2022 06:09 PM
Jan 06 2023 08:24 AM - edited Jan 06 2023 08:25 AM
@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
Jan 06 2023 09:00 AM
Create a rule of type 'Use a formula to determine which cells to format' with the formula
=ISNUMBER(MATCH(221,testdone!$C:$C,0))
Jan 24 2023 12:06 AM
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
Jan 24 2023 01:02 AM
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.
Jan 24 2023 01:57 AM
Apr 18 2023 09:13 AM
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!
Apr 18 2023 11:40 AM
Do the check boxes have a linked cell? If so, which cells do you use for that?
Apr 19 2023 09:12 AM
@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...)
Apr 19 2023 12:30 PM
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?
Apr 19 2023 03:37 PM
Nov 21 2022 03:34 AM
SolutionLet'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.