Turn logged data into fancy plot
Avoid using Active*

You can use Worksheets collection to access a specified Worksheet object represents a worksheet. 

Worksheets("YOUR SHEET NAME")


This can be useful to do explicit job, without changing the active sheet. The same way for ActiveCell, ActiveChart and ActiveChart, you can always access the target object by the collection.

Use With rather than Selection

Aside from error-raising macro problems, a recorded macro is less efficient, because it mimics all the mouseclicks and keystrokes (every cough and camera flash) that occurred while the recording was taking place. A recorded macro clicks on every object to select it, then performs an action on the selection:

Columns("A:A").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority


If you have two or more property or method statements that work on the same object, wrap them in a With / End With block:

With Worksheets("Sheet1").Columns("A")
    ' read / write object's properties here
    ' e.g. add new color scale rule
    .FormatConditions.AddColorScale ColorScaleType:=3
End With


This block can increase the code efficiency and readability.

Check the existence of target object

The macro record is powerful, but it's more difficult to understand whether something exists already. For example, we may not want to apply 10 rules of color scale to the column A.

' drop color scale if exists
For Each fc In .FormatConditions
    If fc.Type = xlColorScale Then
        fc.Delete
    End If
Next


Here's just an example, there must be better logic for treating existing object.



As for your script, here's my suggest edits.

Public Sub Stats2()
'
' Stats Macro
' Give stats to column A and draw chart with trendline.
'
' Modified by Mincong HUANG
'

    ' declarations
    Dim wsName As String
    Dim chartName As String
    Dim srcRange As Range

    ' initialization
    wsName = "Sheet1"
    chartName = "Fancy Chart"
    Set srcRange = Worksheets(wsName).Columns("A")

    ' set format conditions at target column
    With srcRange

        Debug.Print .Address

        ' drop color scale if exists
        For Each fc In .FormatConditions
            If fc.Type = xlColorScale Then
                fc.Delete
            End If
        Next

        ' add new color scale
        With .FormatConditions
            ' add
            .AddColorScale ColorScaleType:=3
            With .Item(.Count)

                .SetFirstPriority

                ' advanced setting (optional)
                ' Optional because these values are the default setting
                ' but you can add other desired color

                .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .ColorScaleCriteria(1).FormatColor.Color = 7039480
                .ColorScaleCriteria(1).FormatColor.TintAndShade = 0

                .ColorScaleCriteria(2).Type = xlConditionValuePercentile
                .ColorScaleCriteria(2).FormatColor.Color = 8711167
                .ColorScaleCriteria(2).FormatColor.TintAndShade = 0
                .ColorScaleCriteria(2).Value = 50

                .ColorScaleCriteria(3).Type = xlConditionValueHighestValue
                .ColorScaleCriteria(3).FormatColor.Color = 8109667
                .ColorScaleCriteria(3).FormatColor.TintAndShade = 0

            End With
        End With
    End With

    ' drop log chart if exists
    Debug.Print Worksheets(wsName).ChartObjects.Count &amp; " charts"
    For Each c In Worksheets(wsName).ChartObjects
        If c.Name = chartName Then
            c.Delete
        End If
    Next

    ' add log chart
    With Worksheets(wsName).Shapes.AddChart2(227, xlLine)
        .Chart.Parent.Name = chartName
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = chartName
        .Chart.SetSourceData Source:=srcRange
        .Chart.FullSeriesCollection(1).Trendlines.Add _
            Type:=xlMovingAvg, _
            Period:=2, _
            Forward:=0, _
            Backward:=0, _
            DisplayEquation:=0, _
            DisplayRSquared:=0, _
            Name:="2 per. Mov. Avg. (Series1)"
        .Chart.ClearToMatchStyle
        .Chart.ChartStyle = 233
        With .Chart.FullSeriesCollection(1).Trendlines(1).Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.25
            .Transparency = 0
        End With
    End With

    ' release memory
    Set srcRange = Nothing

End Sub


By the way, I'm using Excel 2016. So I'm not sure the code compatibility. Please tell me if there's any error.



References


Peltier Tech Blog - Quick Excel Chart VBA Examples
Peltier Tech Blog - Naming an Excel Chart
MSDN - Worksheets Object (Excel)