Conditional Formatting on whole column with variable reference cells

Copper Contributor

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 :)

20 Replies

@LazyBee 

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:

 

2019-11-13.png

 

 

Any ideas why this is happening, please? 

Thankyou :)

@LazyBee 

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)

image.png

and

clipboard_image_0.png

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

 

2019-11-14 (2).png

 

 

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:

 

2019-11-14 (1).png

 

When I change the value (highlighted Yellow) to trigger the formatting it actually changes D3!?

Did I miss something?

 

 

Thanks for your help

 

@LazyBee 

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

image.png

Please check in attached file.

@Sergei BaklanPERFECT! Thankyou so much, really appreciate the additional time spent on helping me clarify my oversight, Thanks! :D

@Sergei Baklan 

 

HI,

 

I also encounter the same problem, but the formula doesn't apply to the other cells. 

@rech_yp 

You have two rules:

image.png

Second one is broken, for the first one range shall start from C2

image.png

 

@Sergei Baklan 

 

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.

rech_yp_0-1635929168796.png

 

rech_yp_1-1635929367275.png

Thanks. 

 

@rech_yp 

From scratch:

1) Select the range, in Conditional Formatting click New rule

image.png

2) Select Use formula

image.png

3) Our range starts from C2. Add formula for this cell and select format

image.png

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)

image.png

 

@Sergei Baklan 

 

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?

 

rech_yp_0-1640833363409.png

 

rech_yp_1-1640833405884.png

 

@rech_yp 

You use another rule. Please use

image.png

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.

Thanks Sergei.. I got it! Happy new year!

@rech_yp , glad to help.

All the best for coming year!

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

 

Screenshot 2023-01-16 185539.png

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!

@ExcelTrish 

 

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:Screenshot 2023-01-17 104020.png

 

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?

@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

ExcelTrish_1-1674052790314.png