Forum Discussion
Conditional Formatting on whole column with variable reference cells
Hi,
I'd like to format the colour fill of multiple cells within one particular column, when it's value is less than a cell in the row above of the next column. how should I do this please?
eg. when the value of D93 is smaller than the value of E92 format fill red. Apply to all D's.
So far I have established that if I type =($D$93<$E$92) then I can make the rule work for cell D93. But how can I replicate this formula throughout the whole of the D column, without typing for each individual cell. Is this even possible? Is there a short cut to copying the rules over?
Thankyou 🙂
Remove the dollar signs and then Copy, Paste Formats down the range you need the format to be applied to .
Thanks Riny_van_Eekelen
I've applied your suggestion and it has worked majoritably, however there are a few that are inaccurate, i.e. same value or higher value any ideas why this is please see attached photo:
Any ideas why this is happening, please?
Thankyou 🙂
Since you apply CF to entire column D, your formula shall be for the first cell of the range, other words for D1, otherwise the logic will be shifted. I'd suggest
=$D1<OFFSET($D1,-1,1)and
SergeiBaklan Having a similar but slightly different problem if you can help.
On the below, I would like column C:
- to be red if today's date is the same as or past the date in column C (If today's date is 17/01/22 and date in C2 is 15/01/22, cell should be red)
- to be yellow if within three days prior to and today's date and date in column C (if today's date is 17/01/22 and date in C2 is 19/01/22, cell should be yellow)
- to be green if "date completed" in column D is prior to the date detailed in column C (if date in D2 is 16/01/22, and date in C1 is 17/01/22, this should supersede previous two conditions, and cell should be green)
Is this possible? Any help very much appreciated!
- Hi LShoesmith.
Set a cell equal to today's date as your reference date: = TODAY() --> Cell E1 for rest of example
To set conditional formatting for Highlight Cell Rules:
Red: C1 <= E1
Yellow: C1 <= E1+3
Green C1 > E1 +3
Check that your rules are in this order as you have in your screenshot. If the yellow highlight rule is before red, then the yellow rule will supersede red for any cell that is less than today's date + 3.
Hope this helps!Thanks for responding! I couldn't get the below to work and I'm wondering if this is possible as there could be conflicting conditions...
I'm trying to change cell C based on today's date, unless there is a date in column D prior to the date detailed in column C.
I'll give you a working example:
- A contract is signed 17/01/22, and we expect a rep to have a follow up call with within 5 days "Expected date".
So B2 has sign off date of 17/01/22, and C2 is auto populated with 22/01/22 (=B2+5)
- As 22/01 approaches (within 3 days of this date) I want C2 to become yellow to alert rep action is needed, so I've used cell value between Today() and Today()+3 and formatted to yellow
- If no action is taken by 22/01/22, C2 should turn red, so I've used cell value <today() and formatted red
- But both yellow and red conditions should be superseded if column D "Date completed" has a date on or prior to expected date, when I'd like C2 to turn green
Here's a screenshot to help:
In this example:
- C2 should be green as date in D2 is prior to date in C2
- C3 should be red as date in C3 is equal or past today's date, with no prior date in D3
- C4 should be red as cell date is past today's date, and date in D2 was not prior to date in C2
- C5 should be yellow as cell's date is within 3 days of today's date
Any wizardry up your sleeve?
