Auto update/run VBA when cell is changed

[chow81]

Can anyone help... I wrote some code to change the plot colors in a scatter chart based on defined criteria, but I need the macro to run automatically each time the cell range is changed. Currently the macro only works if I run it manually. I am new to VBA, am I missing something in the code?

Sub ColorDot()

Dim s As Series
Dim i As Integer

Set s = ActiveChart.FullSeriesCollection(1)

For i = 1 To s.Points.Count
If Cells(i + 2, 18) = "1" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 7, 1)
If Cells(i + 2, 18) = "2" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 200, 0)
If Cells(i + 2, 18) = "3" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 110, 230)
If Cells(i + 2, 18) = "4" Then s.Points(i).Format.Fill.ForeColor.RGB = RGB(34, 229, 1)

Next i

End Sub

 

[JoeMo]

What is the cell range that you want to trigger your macro when it is changed? Are the changes made manually or through formulas in the range?

 

[chow81]

What is the cell range that you want to trigger your macro when it is changed? Are the changes made manually or through formulas in the range?

Hello, the cell range I am targeting is R3:R18 and the changes to these particular cells come from a formula pulling data from another worksheet. For example: =IF(AND('Leadership Assessment'!$G$19="Few (0-50%)"),1,IF(AND('Leadership Assessment'!$G$19="Some (50-75%)"),2,IF(AND('Leadership Assessment'!$G$19="Most/All (75-100%)"),3,IF(AND('Leadership Assessment'!$G$19="All/More (Beyond 100%)"),4,""))))

 

[JoeMo]

Are the changes to the Leadership Assessment worksheet that affect the formula cells in R3:R18 made manually or via formulas?

Manual changes can be addressed with a Worksheet_Change event macro setup to call your macro whenever pertinent cells on the Leadership Assessment sheet are changed. The alternative is to use a Worksheet_Calculate event macro on the embedded chart worksheet and call your macro each time a calculation occurs, but this may lengthen calculation time.

 

[chow81]

Are the changes to the Leadership Assessment worksheet that affect the formula cells in R3:R18 made manually or via formulas?

Manual changes can be addressed with a Worksheet_Change event macro setup to call your macro whenever pertinent cells on the Leadership Assessment sheet are changed. The alternative is to use a Worksheet_Calculate event macro on the embedded chart worksheet and call your macro each time a calculation occurs, but this may lengthen calculation time.

Hi JoeMo they are made manually by selecting a response from a dropdown on the Leadership Assessment.

 

[JoeMo]

Then you can probably use a Worksheet_Change event macro that is placed in the Leadership Assessment sheet. This will likely require that you make some changes to your Sub ColorDot macro. If you can post some representative data for the embedded chart worksheet and the Leadership Assessment worksheet using XL2BB you will increase your chances of getting some help with the event macro and and any necessary changes to ColorDot. XL2BB - Excel Range to BBCode

 

[chow81]

Then you can probably use a Worksheet_Change event macro that is placed in the Leadership Assessment sheet. This will likely require that you make some changes to your Sub ColorDot macro. If you can post some representative data for the embedded chart worksheet and the Leadership Assessment worksheet using XL2BB you will increase your chances of getting some help with the event macro and and any necessary changes to ColorDot. XL2BB - Excel Range to BBCode

Unfortunately, my organization prevents me from downloading and it also blocks me from sharing internal files externally. Is there any chance you could help me based on what I provided above?

 

[JoeMo]

I think that would take a larger investment of my time than I am able to provide.