Applying Conditional Formatting according to date

[andy_tap]

Hi,

I have CF applied to the column for the date 5-5-2016 and it colors the cell RED according to the formula.I am going to use the Format Painter and copy the CF across the sheet to column V.As i have no values for the dates 12-5-2016 onwards it colors the cells RED. Is it possible to apply the CF across the sheet to column V but only activate CF if there are values in the cells?

Thanks

Andy

[Crooza]

With E3 selected go to CF (deleate ALL the rules you've got and create this one rule)

=AND(E3<$A3,E3<>"")

Then use painter to copy to other cells

[andy_tap]

Hi Crooza

Thank you and works fine. Does this part of formula work by seeing the cell empty (E3<>"")

[Crooza]

Yep. In mathematics you'd write not equal to or an equal sign with a slash through it. There is no equivalent sign in coding so computer programers have used less than or greater than"<>" for many years to show if something is not equal to something.

So if E3 is not equal to "" then it is saying it is not blank. 'Not blank' is a double negative so it really means that there is actually something in that cell.

[andy_tap]

Is it possible that when values are entered for the week 12-5-2016 that the CF then is removed from any column before that date. other then removing the values would this require VBA?

Thanks

[Crooza]

If you change the rule to this

=AND(LOOKUP(25^25,$E3:$V3)<$A3,LOOKUP(25^25,$E3:$V3)=E3,E3<>"")

You should get the outcome you want. ie it only highlights the last cell entered if the last cell is less than the limit required

[andy_tap]

Hi Crooza

If possible could you post an example in the workbook from post#1...

Also how does the formula work please?

Thanks

[Crooza]

The formula looks at the last entry in the column. lookup(25^25,range) looks up the last number (25^25 is just a big number) and it returns the last entry in the row. The formula checks that this last number is lower than the limit in column A, isn't blank and also checks that it is the last number in the row that is being tested.

Does that make sense?

Now attachments haven't been working well for me for a while. I'll give it a go but it's hit and miss with this site and my computer at present.

[andy_tap]

Thanks Crooza

[Crooza]

Great. Glad it worked. You should mark as solved if that addresses your issues

[andy_tap]

Hi Crooza

I created a test sheet but I could not get it to work like your example...have I made an error somewhere?

How would you CF U58:U63<E58 (these 6 cells relate to E58....is this possible with current formula?

Thanks

[Crooza]

I'll try to look at this tomorrow

[andy_tap]

Ok....thank you

[Crooza]

Sorry for delay.
Finally got a chance to look at this for you. The attached will only conditionally format the last cell in a row of cells that meets the criteria.

To address your question of the cells in rows 58 to 63, the easiest way was to simply populate those cells with the 65,000 figure in column E. If you want the CF to only reference one cell that can be done by locking the cell using the '$' symbol but it will mean that the CF applied to those cells is different from the rest of the spreadsheet so will result in inconsistencies. I can do this if you want but I wouldn't recommend it.

[andy_tap]

Thanks crooza....appreciate the help