Conditional Formatting not applying to cells with formula

[DarrylBurge]

I have the following CF rule applied

=AND(C2<>"",C2=MIN(dnrCol),ROW(C2)>=COUNTA(C:C)-28)

The rule is applied to C2:N1000

If the cells in a row contain numbers the CF applies correctly. Columns E, H, K and N contain a formula, E=C2+D2, H=F2+G2 etc, the CF does not apply.

If I replace the formula with a number, CF still does not apply.

I have a similar CF on columns O-R and the same issue happens, so it is not explicit to the CF on C:C-N:N

Any ideas as to why CF will stop working on these columns?

[HansDouwe]

Your conditional rule can only fire in the last 28 non-empty fields of a column.
Because the formula in column E has replaced empty cells with 0 from row 37 onwards, this conditional rule can no longer be applied in E7.

[DarrylBurge]

Is there any way of adding the (e2)=C2+D2 down the column without affecting the CF?

I have tried =(IF(C2="","",C2+D2)) but I assume that ANY formula will be seen as an entry?

[JeteMc]

It might be helpful to explain in words what determines which values in column E should be highlighted.
I also don't understand what dnrCol is supposed to do. I copied the "refers to:" formula into another column and it seems that it just returns the values that are in column C for rows 9:36.

[DarrylBurge]

I also don't understand what dnrCol is supposed to do.

This thread https://www.excelforum.com/excel-for...ml#post5906664 is where I was given the dnrCol to use.

It might be helpful to explain in words what determines which values in column E should be highlighted.

The highest and lowest values in each column for the last 28 days

[HansDouwe]

What should be considered the last day in the following example?

Thursday January 18 or Wednesday January 24.

Or does that differ per column?

[DarrylBurge]

What should be considered the last day

From my use of the spreadsheet the last entry in considered as the last day. I am happy for this to stay, as if today() was considered the last day, on a Monday i would have 3 blank lines (Fri, Sat & Sun), and in the week I work a day behind, entering data for what was completed yesterday to midnight

[AliGW]

@DarrylBurge

For future reference, this should have been posted in the thread wrere you were given the formula originally, not in a new thread, as it's not actually a new problem.

You have responses, so I won't close you down this time, but please bear this in mind in future. Thanks.

[HansDouwe]

In your example is the last entry not always on the same day. See image post #6.

Does each column have its own last day? Or is this example unrealistic?

This is important to know.

[DarrylBurge]

Attached is the data I have updated this morning.

X30 - AE30 is incomplete as I am missing a form with figures on it, but ordinarily that would also have been completed.

[AliGW]

It's also important to note that this has nothing to do with the cell contents being generated by a formula: the formula is returning a numerical value, so the CF will work once properly defined. The fact that the column uses a furmula here is a red herring.

Please answer all three of Hans' questions in post #9.

[DarrylBurge]

this has nothing to do with the cell contents being generated by a formula:

in a way it does. If I add a formula in E, H, L, N or R then this is seen as an entry, and so the CF will only look to the last 28 entries. If I autofill these formula down to my last date, then the last date row is seen as my last entry for these columns, and so these 28 rows will have the CF applied to them.
if I delete the formula below the line I am currently working on, then CF applies to the previous 28 rows, and so is applied "correctly" for the data i want it to be applied to.

Please answer all three of Hans' questions in post #9

Up-to-data worksheet supplied to answer Hans' questions.

[AliGW]

Change formulae to return BLANKS in unused rows thus:

=IF(OR(C2="",D2=""),"",C2+D2)

Then change ALL instances of COUNTA in the named range and CF rules to COUNT.

I have done column E for you in the attached.

It is NOT the formulae per se: it's what you are telling them to do that is the problem.

[DarrylBurge]

Hi Ali

I have made the change, but it still looks as though the CF takes "" as an entry??

If I copy the formula E32 to E33 then E11 becomes the lowest figure in the 28 rows prior to E33 - which I can't understand because the COUNT is being carried out on C:C not E:E

[AliGW]

You have NOT changed the named range as instructed.

If you don't follow instructions, you won't get the same results.

I attached an example to my last post with it working in column E.

[DarrylBurge]

Well, I cannot see where I am making the change incorrectly.

On the attached I have included yours and my CF Formulas and they look identical, yet in my spreadsheet E11 is highlighted, but E4 in yours.

Forget that, i missed the NAMED RANGE

[HansDouwe]

Please try in E2 and copy down and copy to H2, K2 and N2 and copy down:

IF(C2="","",C2+D2)

Please try CF-rule red applied to $C$2:$N$348

=AND(C2<>"",C2=MIN(dnrCol),ROW(C2)>=(COUNT(C:C)-28)+ROW(C$2))

Please try CF-rule green applied to $C$2:$N$348

=AND(C2<>"",C2=MAX(dnrCol),ROW(C2)>=(COUNT(C:C)-28)+ROW(C$2))

Please try CF-rule green applied to $O$2:$R$348

=AND(O2<>"",O2=MIN(dnrCol),ROW(O2)>=(COUNT(O:O)-28)+ROW(O$2))

Please try CF-rule red applied to $O$2:$R$348

=AND(O2<>"",O2=MAX(dnrCol),ROW(O2)>=(COUNT(O:O)-28)+ROW(O$2))

[AliGW]

Named range:

Darryl - =INDEX('RAW DATA'!F:F,MAX(2,COUNTA('RAW DATA'!F:F)-27)):INDEX('RAW DATA'!F:F,COUNTA('RAW DATA'!F:F))

Ali - =INDEX('RAW DATA'!F:F,MAX(2,COUNT('RAW DATA'!F:F)-27)):INDEX('RAW DATA'!F:F,COUNT('RAW DATA'!F:F))

That's the third time I've mentioned the named range!!!

EDIT: I see you've got it.

Please mark as solved, etc., but ask here if you have any further issues with these formulae.