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.
Create a rule of type 'Use a formula to determine which cells to format' with the formula
=ISNUMBER(MATCH(221,testdone!$C:$C,0))
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
To make the following work, enter dates in the month cells in row 1, formatted as mmm to display the abbreviated month name.
For example, in L1: 10/1/24, in M1 11/1/24 etc.
Select L2:X12 or further to the right and down.
L2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=AND($H2<>"", EOMONTH(L$1, 0)>$H2)
Click Format...
Activate the Fill tab.
Select black as color.
Click OK, then click OK again.Select L1:X100 (or further if you like).
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
In the box next to it, enter the formula="N/A"
Click Format...
Activate the Fill tab.
Select black as fill color.
Click OK, then click OK again.I have a spreadsheet with quarterly invoices. I want all cells in a row to turn black after the date in column H (ie where the N/As are in the first 5 rows.) and I want my range to be columns L,M,N,Q,R,S, U,V,W. (I will have different numbers of rows depending on the sheet and the year adn would rather not have to specify range.) I'm trying to tell it to look at a cell, check the header row to determine if that date is after the date in Column H, and if so change all other cells in date columns black for the rest of the row. And I want it repeated for the whole sheet. I just can't figure out how to code that.
This is what I want it to look like at the end.
Is there some form of conditional formatting or macro use that I could make this happen?
Thanks!
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 ...
