Eliminating Color filled cells from the formulas

UDFNewbie

New Member
Hello All,

I am new to this forum and UDF's (which is what I think I need for the following) so I appreciate the advice:

In the below picture, I am doing several things:

1) Tracking my consumption of different materials (rows 3&4) vs. plan (rows 1&2)
2) Calculating my purchased consumption (cell showing 25% to the right)
3) Forecasting Full year spend in the (GREEN). I am entering values and forecasting my total full year spend. ISSUE: When I forecast the rest of year in green, it updated the cell with the 25%

QUESTION:
1) Is there a way to have the cell with the formula for 25% calculated only those cells that are not colored green? (NOTE: The 25% is driven really just by the row titled "fixed volume").
2) This same 5 rows below are repeated many times for other materials. Will the solution for #1 need to be done on each "25%" formula on each tab of the workbook?

Thanks in advance for the communities help!

UDFNewbie

steve the fish

Well-known Member
You should be aware that changing a cell's color does not trigger calculation.
If you have this formula set up to ignore green and the user makes a cell green, the formula will not re-calculate and the value seen will not reflect the change in color. (Making the UDF volatile will not fix this.)
Using color as data in Excel is fraught with problems and is contra-indicated.
Hi Mike. I said that in post 6 as i knew this to be the case. However weirdly when testing and using the format painter to paint the cell is was causing recalculation which i surprised by. Im not sure what the painter does to cause a recalc but it was happening. Using 365.

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

steve the fish

Well-known Member
What Mike is saying is it will not recalc by using formatting alone. Say you have a value in a cell then paint it green. In theory that would not cause the formula to recalculate. If you lose the cell formatting then type a number in the cell the formula will recalculate. Best way to try is to test and confirm it to yourself.

UDFNewbie

New Member
Steve, Thanks for your patience with me! I Did the following:

1) updated my formula for the 25% to add the _NG: =IF(O5=0,0,IF(AVERAGE_NG(O5:Z5)>AVERAGE(O3:Z3),IF(AA5>AA3,1,AA5/(AVERAGE_NG(O5:Z5)*12)),AA5/AA3))

2) added the VBA (as seen in pic below) by doing ALT+FN11 and then "insert module". When the window popped up I added the below code with one edit. i chnaged the R from 205 to 204. No other changes.

Result: When I add numbers into Row 5 (see post #3), the % keeps changing (getting higher, as it should). I tried clearing cells and colors and adding color then numbers back in, but same result. The % changes instead of ignoring because it is green. I think I took warning to your post 12 above but obviously I am doing something wrong. Any advice?

steve the fish

Well-known Member
This looks like one of the warnings we suggested about formatting not being the best way to calculate things. However first things first check that the green you are using matches the RGB in the function.

UDFNewbie

New Member

Yea I was worried about that. unfortunately, I did check that before posting the last message. 204,255,204. Something new in that window (at least for me is the Hex# CCFFCC). Is there something there that we have to be careful about? Pic below:

mikerickson

MrExcel MVP
To show when a cell is being calculated, you could put =RAND() in a spare cell and then change the color of a cell. if the value returns by RAND changes, calculation has been triggered.
The format painter is a Paste operation, which does trigger calculation. (Just as copying a cell and pasting it onto itself does.)

The reason that RAND recalculates but your UDF does not, is that RAND is a volatile function, it recalcualtes any time any cell in the workbook changes. Your UDF, like most functions, recalculates only if one of the precedent cells changes.
To make your UDF volatile put the line
VBA Code:
``Application.Volatile``
into the function.

steve the fish

Well-known Member

Be sure they are all the same even if they look they same they may not be. Now if you understand that formatting doesnt cause recalculation then you should be able to get it to work. So if you want to remove the green colour then remove the formatting first then add the number. It should recalc. If it isnt show the worksheet with the ranges and the formula bar showing the formula in use.

steve the fish

Well-known Member
Seeing as you are seeing the pitfalls of using colours to perform calcs maybe you should use a helper row. Maybe use 1s for calc column and 0s for dont. They you can use an AVERAGEIFS native formula.

UDFNewbie

New Member
To show when a cell is being calculated, you could put =RAND() in a spare cell and then change the color of a cell. if the value returns by RAND changes, calculation has been triggered.
The format painter is a Paste operation, which does trigger calculation. (Just as copying a cell and pasting it onto itself does.)

The reason that RAND recalculates but your UDF does not, is that RAND is a volatile function, it recalcualtes any time any cell in the workbook changes. Your UDF, like most functions, recalculates only if one of the precedent cells changes.
To make your UDF volatile put the line
VBA Code:
``Application.Volatile``
into the function.
Thanks Mike. Is there somewhere in particular in the code that I should place that?

mikerickson

MrExcel MVP
I ususally put it in the function right after the declaration lines.

Replies
2
Views
43
Replies
1
Views
39
Replies
3
Views
41
Replies
3
Views
54
Replies
5
Views
103

1,129,795
Messages
5,638,379
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back