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

jgjengr

New Member
Joined
Nov 16, 2015
Messages
1
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)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,095,804
Messages
5,446,563
Members
405,407
Latest member
apat

This Week's Hot Topics

Top