Getting a line in a graph to change colors

[nathan2314]

Good Morning all :hi:

I just finished writing some vba code that will change the color of a line in a graph (x line) if it drops below another line (y line) for any value of x vs. y.
Now the graph is tied to several different cells in my spreadsheet at different places (other than the straight values that the graph reads in to create the lines). Cells that when the values are changed they do different calculations/manipulations and change the actual values that the graph reads in to create the lines.
Ok, so now I want the spreadsheet to run this vba code anytime any cell that indirectly/directly affect the graph is changed. How can I do that. I know I can create a button attached to the macro that when clicked will run the macro but is there anyway to have the macro run when someone just changes a cell a value? I'd have to do this for several different cells. Or maybe a better way ?

help! :)

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1:C20")) Is Nothing Then
'your code
End If
End Sub


change the range to suit.

[nathan2314]

Ok that makes sense. I incorporated that but i still cant get it to work. How come I can't step through the VBA code using th 'F8" Key with worksheet_change() to debug.
Here is my whole code. If I run Linecolor() by itself it works fine. But when I change a cell in my spreadsheet("Long Term Input Data") nothing happens??
Private Sub Worksheet_Change(ByVal Target As range)
If Not Intersect(Target, Me.range("A1:Z50")) Is Nothing Then
Call LineColor
End If
End Sub

Sub LineColor()
Application.ScreenUpdating = False
Sheets("Personnel Availability").Select
Rng = range("UpdateGraph2").Columns.Count

ReDim x(Rng - 1)
ReDim y(Rng - 1)

For i = 1 To Rng - 1
x(i) = range("A13").Offset(, i).Value
y(i) = range("A24").Offset(, i).Value
If x(i) < y(i) Then
GoTo changelinecolor
End If

Next i
Sheets("Long Term Input Data").Select
ActiveSheet.ChartObjects("Chart 22").Activate
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 6
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = True
End With
Exit Sub

changelinecolor:
Sheets("Long Term Input Data").Select
ActiveSheet.ChartObjects("Chart 22").Activate
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = True
.MarkerSize = 3
.Shadow = True
End With
End Sub

[mdmackillop]

How come I can't step through the VBA code using th 'F8" Key with worksheet_change() to debug.

Put a Break on the first line of the sub, then make a change on your sheet.

Can you post your workbook?