VBA Code to Format Individual Data Points Based Upon Adjacent Cell Values

[jgjengr]

Suppose I have a data set that looks like this:

Position, Reading, Gage, Setting
1.3, 24, 1, A
1.6, 27, 2, A
1.7, 22, 1, B
1.8, 21, 2, B
1.1, 25, 2, A

Naturally, I'm working with a bigger data set, but key points are we have an independent variable "Position", a dependent variable "Reading", and two categorical variables "Gage" and "Setting".

If I have a scatter plot in excel (X=>Position, Y=>Reading), how would I write a macro to set the color of the data point markers based upon the value in "Gage" column and the type of the data point markers based upon the value in the "Setting" column. I'm having trouble figuring out how to identify the location of the cell for a given point object. I've found similar code for changing whole series' format, but it relies on the formula for the series object. I'm not aware of the point object having a formula to "borrow from".

Here's that example:

FormulaSplit = Split(MySeries.Formula, ",")

Set SourceRange = Range(FormulaSplit(2)).Item(1)

 

[Rijnsent]

Hi jgjengr,

what you're looking for is basically something like this: Excel ScatterPlot with labels, colors and markers
A non-VBA alternative: Conditional Formatting of Excel Charts - Peltier Tech Blog

Hope that helps,

Koen