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
Jan 23 2024 07:00 AM
Feb 29 2024 11:18 PM
Mar 01 2024 04:48 AM
You can use conditional formatting referring to the values of the cells on Sheet2, just like when you enter the data there directly.
Mar 13 2024 03:13 AM
@Hans Vogelaar what if I need a cell to change to be white if the adjacent cell is left blank I tried =D3=“BLANK” but that didn’t do the trick
Mar 13 2024 04:26 AM
Mar 13 2024 05:07 AM
Mar 14 2024 03:02 AM
@Hans Vogelaar that did the trick
now one more question I want C3 to change green if D3 falls between the value of 8.00-9.49
i already have the formulas for if it’s less than 7.99 it turns yellow =$D3<(7.99), and more than 9.5 it turns red =$D3 >(9.5) in place.
any idea what formula might work for 8.00-9.49?
Mar 14 2024 04:27 AM
Apr 12 2024 01:47 PM
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!
Apr 12 2024 01:52 PM
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.
Apr 12 2024 02:32 PM
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.
Apr 19 2024 11:57 AM
What if the same conditional format can be based on two differing texts in a single cell, such as =$F2="Closed" as well as "Closed-Ext". I'm not sure how to combine that in one conditional formula...
Apr 19 2024 12:01 PM
Apr 19 2024 12:17 PM
Apr 19 2024 12:22 PM
Apr 19 2024 12:50 PM