Data Validation based on formula

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?

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.

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.

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.

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.

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.

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.

Thanks, appreciate the help.

You're welcome!