Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...If there has ever been a justification needed for access to the net during working hours, just referring to this site should suffice. Fantastic!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > MIS/IT > Office Suites > Microsoft: Office Forum

Excel chart; marker color based on cell value

thread68-1688540
Read More Threads Like This One
beakerboy (TechnicalUser)
18 Jul 12 8:03
I have a worksheet with three columns; an x value, a y value and a confidence value. I'd love to be able to plot the x and y in a scatter plot, and use the confidence value somehow in the marker color. I could easily add 3 more columns for RGB values and manipulate them using the confidence if there's a way to embed RGB values into the chart.

Thanks for any advice.
Kevin
SkipVought (Programmer)
18 Jul 12 8:31
hi,

Charts can be configured such that EMPTY cells do not plot.

Picture your table with several extra oolumns that will be used for color, each column represents ONE COLOR.

In each column is a formula with the logic...

IF [the confidence value] is within some range THEN the value OTHERWISE EMPTY

So depending on [the confidence value], [the confidence value] fill appear in only ONE COLOR COLUMN. You do NOT plot [the confidence value], rather the color columns.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

beakerboy (TechnicalUser)
18 Jul 12 8:50
I guess that might be the simplest way. I was trying this:

CODE --> VBA

Sub set_color()
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Select
    For i = 1 To 24
        ActiveChart.SeriesCollection(1).Points(i).Select
        Dim color
        color = Cells(i, 4).value
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(color, 0, 0)
            .Transparency = 0
            .Solid
        End With
    Next i
End Sub
But it sets every cell to the same default blue color.
beakerboy (TechnicalUser)
18 Jul 12 9:16
Got It. I rearranged the function and it works

CODE --> VBA

Sub set_color()
    ActiveSheet.ChartObjects("Chart 3").Activate
    For i = 1 To 24
        Dim color
        color = Cells(i, 4).value
        ActiveChart.SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(color, 0, 0)
    Next i
End Sub
SkipVought (Programmer)
18 Jul 12 9:17
Well you did not post in forum707: VBA Visual Basic for Applications (Microsoft), so I did not suggest a VBA solution.

Besides, this is a perfectly good approch. I often use this kind of technique for plotting the demand and supply picture for a part in an MRP system. I can show individual demands and supplies as distinct columns (minus RED & plus GREEN) on the primary axis and the total shortage or surplus as columns with ZERO gap (minus PINK & plus LT GREEN) on the secondary axis, and you can IMMEDIATELY see the area(s) where attention must be directed.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Office

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close