Data Validation based on formula

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.


Open in new window


Open in new window


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor 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?
RWayneHAuthor 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.
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RWayneHAuthor 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 ExpertCommented:
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.
RWayneHAuthor 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.
RWayneHAuthor 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 ExpertCommented:
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.
RWayneHAuthor Commented:
Thanks, appreciate the help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.