Forum Discussion
How do I make excel change the colour of a cell depending on a different cells date?
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...)
wjwhittle , you are welcome
SergeiBaklan Thank you! works perfectly
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.
- Hi 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 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 ...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.
- Yes - the range is continually expanding each day - more data ...
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?
- But 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. 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.
