Conditional Formatting from within a formula

[Chameleon1]

Hi,

I am trying to find a way to insert a conditional format(I think?) into a worksheet.

I would like for the cells contained within my formula to all fill green once I have completed the formula.

eg.

I have a list of motor vehicle expenses which spans 100 rows with a total at the bottom.

I create a formula below the total which is just a sum of (various non-deductible) cells within the 100 above - lets say 25 cells in total.

Once I have completed my sum formula of the 25 cells I would LOVE to just hit enter and then those 25 cells that make up the formula all turn green.

Currently I then have to go back and select those 25 cells again and then just fill them green.

I have tried conditional formatting but I can't seem to get it right, it only ever picks up my 1 cell that has a formula in it, instead of the cells that make up the formula.

Any help would be appreciated, this can be quite a long task when I sometimes have thousands of cells to go through.

[#2]

The crux of this problem is finding out what makes you decide to sum those specific 25 cells. If there is any logic to that which we might check against the data of those cells or cells on the same rows we can cook up a formula.

[#3]

Hi @Jan Karel Pieterse ,

Unfortunately there is no logic behind it. It would differ depending on what I am doing. It would change for each worksheet as well. It just depends on what tax law I am applying as to what is or isn't deductible and I can't just take them out of the workpaper I need to show both totals.

I was hoping there would be a way other than to do it twice (eg, select the cells to create the formula and then select the cells again to format)

[#4]

I would add a column into which you enter a 1 for each row you want to include in the sum and use SUMIFS to do the conditional summing. You can then also use a conditional format to color the cells that you are currently adding.