Nov 13 2019 06:47 AM
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 :)
Nov 13 2019 07:01 AM
Remove the dollar signs and then Copy, Paste Formats down the range you need the format to be applied to .
Nov 13 2019 09:11 AM
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 :)
Nov 13 2019 12:02 PM
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
Nov 14 2019 05:35 AM
@Sergei Baklan Hmm, this seems to have complicated things slightly. I applied the example given to cell D5 "=$D5<OFFSET($D5,-1,1)" and now the 1st cell is filled red.
This is understandable as E4 has letters not numbers, but it hasn't resolved the original problem. Infact it seems to have delivered a new one:
When I change the value (highlighted Yellow) to trigger the formatting it actually changes D3!?
Did I miss something?
Thanks for your help
Nov 14 2019 12:35 PM
First, in this variant we apply conditional formatting to entire column D, not to concrete cell or range.
To exclude headers we may modify rule formula as
=($D1<OFFSET($D1,-1,1))*(ROW()>5)
When it'll be like
Please check in attached file.
Nov 14 2019 02:22 PM
@Sergei BaklanPERFECT! Thankyou so much, really appreciate the additional time spent on helping me clarify my oversight, Thanks! :D
Nov 15 2019 11:31 AM
@LazyBee , you are welcome
Oct 11 2021 02:26 AM
HI,
I also encounter the same problem, but the formula doesn't apply to the other cells.
Oct 24 2021 01:23 PM
Nov 03 2021 01:50 AM
Hi Sergei,
Sorry still unable to get the formula you shared. How do we apply the formula for all the cells, as you mentioned 2nd formula is broken.
Thanks.
Nov 03 2021 02:05 PM
From scratch:
1) Select the range, in Conditional Formatting click New rule
2) Select Use formula
3) Our range starts from C2. Add formula for this cell and select format
4) Ok and Apply. Check format work, cell in formula is first cell of the range and formula itself is not in quotes (it means you tried to add wrong formula which Excel doesn't recognize)
Dec 29 2021 07:04 PM
Thanks Sergei. It works for the first cell. But when I try to copy and paste to apply the same formatting to to other cells within the same column, it doesn't work, as it is still comparing to the first cell.
What should I do, to apply the same conditional formatting for other cells, which it will compare accordingly?
Dec 30 2021 02:18 AM
You use another rule. Please use
add formula to the rule
=S140 >= R140
applied to the range
$S$140:$S$145
On practice first select range, add new conditional formatting rule , select type of the rule, add formula for the first cell of range correctly using relative references, apply format.
Dec 30 2021 09:02 AM
Dec 30 2021 09:15 AM
@rech_yp , glad to help.
All the best for coming year!
Jan 16 2023 11:07 AM
@Sergei Baklan 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!
Jan 16 2023 03:27 PM
Jan 17 2023 02:43 AM
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?
Jan 18 2023 06:42 AM
@LShoesmith When I've done this before, I created a separate column to indicate that a task was completed. I then created an IF statement based on that completion column to indicate if the task was Done or the # of days remaining before it was due. For cell values that Contain 'Done', conditional formatting turns that green. The yellow and red conditions apply as normal.
Screenshot from file I use