Data Validation based on formula

RWayneH
RWayneH used Ask the Experts™
on
I have a variable saved as a value, it is an average.  lets call it MyAvg.

I would like to apply a data validation on a column (D) that uses MyAvg that:

If value is >15% of MyAvg = Red
If value is between >10 of MyAvg but <15% of MyAvg = Yellow
If value is between <10 of MyAvg and -10% of MyAvg = Green

How would I write a formula in the Data Validation, to use MyAvg?
Ideally, or another option I would like to explore this using some VBA code against a cell... but was going to try both ways.  

Please advise and thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Maybe you are talking about the Conditional Formatting.
To achieve this, select the whole column D and make New Rules for conditional formatting using the following formulas.

Red:
=AND(ISNUMBER(D1),D1>MyAvg*0.15)

Open in new window

Yellow:
=AND(ISNUMBER(D1),D1>MyAvg*0.1,D1<=MyAvg*0.15)

Open in new window

Green:
=AND(ISNUMBER(D1),D1>-MyAvg*0.1,D1<=MyAvg*0.1)

Open in new window

All the above formulas assumes that the MyAvg is a named range which holds the value you are comparing column D values with.

Author

Commented:
Does it matter if MyAvg is a single value?  I do have another name that is a range that I used to determine the MyAvg, that I can use?

Author

Commented:
I actually prefer the vba to apply to a cell, because as data is appended to the sheet the averages will chg...  I only add 4 lines to the table at a time, and would like to  evaluate a cell to a single MyAvg value, changing the color based on the three rules.  I have 6 different MyAvg values for six different columns, but I can apply the one example to each column.  Then I will apply a differnt filter and do it again.

Is it possible to try the vba cell to color option?  I already have all the averages defined to elvaluate a cell based on a cell.  Also with this option I do not have to refilter the data.

I would like to evaluate ActiveCell value to the MyAvg value, to get its color....  I can move the ActiveCell with offset commands.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I think that the two value comparison can be done in vba, to give the cell a color.  One being the ActiveCell and the other being a collected average, saved as a variable.  The more I think through how this is going to be used the more I am not going to be able to do this by the whole column at a time.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Does it matter if MyAvg is a single value?  I do have another name that is a range that I used to determine the MyAvg, that I can use?
MyAvg is supposed to be a named range based on a single cell that means a single value. It can be replaced by any single cell named range.

Author

Commented:
Ok, so what happens when I chg the filter and apply the condition again?  As was evaluating and as I review the process that is going to be used, doing this to the whole column is not going to work....  we do not want to chg any existing colors that have been applied previously.  I really would like to use the vba scenario to compare ActiveCell to MyAvg so it leaves those cells alone that were already colored from a previous running of the file.  Can we do that?  I need some help writing that vba to do this.

Author

Commented:
So are you bailing on this question?  My question states that I would like to look at both options...  Conditional Formating (which is now not going to work) and the vba option to compare two values (MyAvg and the ActiveCell) and color the cell based on the result.  I need a hand with the vba that would compare the two cells, but I have not heard anything for a couple days?  Please advise and thanks.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Sorry! Normally I am busy in my projects so don't get much time to come here and help for free.
I answered your original question considering your original description but don't have time to sort out what if kind of things.
If you need a devoted solver, I would suggest either you should open a Gig project or hire someone on Live.

Author

Commented:
Thanks, appreciate the help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial