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
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!
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.
