I tried to found VBA macro for adjusting point in XY chart base on value but without success.

I would like to have VBA Macro that on running (button) change all points which are not in tolerance defined by cell.

Can you please help me? Thank you, Jiří.

I was not able to try anything

CodePudding user response：

Don't use VBA for this task. Instead, add a column to your worksheet which has a value if a limit is exceeded, and plot this column as a new series in the chart with the marker that signifies Out of Control.

I've simulated your situation below. There are columns for point number (the X value), two horizontal red lines, two horizontal dashed green lines, and one horizontal dashed black line. There is a column for the data you are tracking, and a column for the calculated outliers.

The formula in cell H2 is

=IF(OR(G2<B2,G2>F2),G2,NA())

If the value in column G is lower than the bottom red line or higher than the top red line, the value appears in column H; otherwise, the #N/A error appears in the cell. #N/A is not plotted in a line chart, so you will only get the out-of-control marker when the formula places a value in this column.

You can write a formula to include any definition for a "bad" point, including any of the other control chart rules you may be interested in tracking.

Plot column G on the chart, format it with no line but with the marker style you want.

All I have to do is add data to the chart. If any further points fail my test, they are highlighted with the appropriate marker. Easier, faster, and more reliable than VBA.