Forum Discussion
How do I make excel change the colour of a cell depending on a different cells date?
- Nov 21, 2022
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.
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.
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...)
- SergeiBaklanNov 04, 2023MVP
wjwhittle , you are welcome
- wjwhittleNov 03, 2023Copper Contributor
SergeiBaklan Thank you! works perfectly
- SergeiBaklanNov 03, 2023MVP
For the blue you may use conditional formatting rule with formula
=COUNTIF($H:$H,B1)
applied to $B$1:$F$50.
Another similar rule is for red.
- wjwhittleNov 03, 2023Copper ContributorHi Sergei,
This thread seems the closest I can find, I have a list of students in Column H and a List in Column I
I need to swap in and out between H and I.
When in H I want cells B1: F50 if they match what is in H to be blue and if in I to be red
What conditional formatting formula do I need so I don't need to individually match and change the colour if I move into the other column?
TIA - MrKasifsOct 11, 2023Copper Contributor
When I insert a column - it seems to inherit the formatting - so its AutoMagic !
I keep the most current stats on the left of the sheet next to the legend - the Old data moves out to the right ... - SergeiBaklanOct 10, 2023MVP
That means you may apply the rule to the range $B$2:$XFD$1000000 if you don't care about performance, but it's better to take more realistic gap.
- MrKasifsOct 10, 2023Copper ContributorYes - the range is continually expanding each day - more data ...
- SergeiBaklanOct 09, 2023MVP
In previous post
"Tomorrow I will insert the column for the October 9th"
That means you are expanding the range, isn't it? Or you always have exactly the same number of columns?
- MrKasifsOct 09, 2023Copper ContributorBut you see - I do not want to expand the range - the sheet above depicts the data exactly as I wanted
The range will always Only be the column to the right of the column to be compared. - SergeiBaklanOct 09, 2023MVP
You may expand you range to the right as necessary (here is to AZ column) and additionally check if next cell is not empty
Rule iterates all cells within the range. Since we use relative references, B2 is compared with C2, C2 with D2, D2 with E2, etc. to the right and down.
You may check in attached file.