SAS BLOG

Make wykres transparent ;>

Friday, July 10th, 2009 | VBA | No Comments

Sub make_chart_transparent()
    With ActiveChart
        .ChartArea.Border.LineStyle = xlNone
        .ChartArea.Interior.ColorIndex = xlNone
        .PlotArea.Border.LineStyle = xlNone
        .PlotArea.Interior.ColorIndex = xlNone
        .Legend.Delete
     End With
    
     With ActiveChart.Axes(xlCategory)
        .MajorTickMark = xlNone
        .MajorTickMark = xlNone
        .TickLabelPosition = xlNone
     End With
   
    With ActiveChart.Axes(xlValue)
        .MajorTickMark = xlNone
        .MajorTickMark = xlNone
        .TickLabelPosition = xlNone
     End With
    
     For Each ax In ActiveChart.Axes
        ax.HasMajorGridlines = False
        ax.Border.ColorIndex = xlNone
     Next
    
    For Each sr In ActiveChart.SeriesCollection
        sr.Border.LineStyle = xlNone
        sr.HasDataLabels = False
    Next
               
End Sub

Kolorowanie punktów danych w serii, wykres xls

Sub kolorowanie_serii()
Dim sr As Series

odliczaj = val(InputBox(”Odliczaj co -n- kolumn”))

With ActiveChart
    For Each sr In .SeriesCollection
         For i = LBound(sr.Values) To UBound(sr.Values)
            If i Mod odliczaj = 0 Then
                sr.Points(i).Interior.ColorIndex = 35
            End If
        Next i
    Next
 End With
End Sub

Tags: excel, VBA

VBA - Grupowanie kolorem

Wednesday, July 8th, 2009 | VBA | 1 Comment

Sub grupuj_kolorem()
Dim row As Range
Dim target As Range
Dim x As Integer
Dim licznik As Integer

col_number = Val(InputBox(”Podaj numer kolumny do grupowania”))

Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

For Each row In Selection.Rows
    Set target = row.Range(Cells(1, col_number), Cells(1, col_number))
    If target.Value <> target.Offset(-1, 0).Value Then
        x = Abs(x - 1)
        licznik = licznik + 1
    End If
    If x = 0 Then
        row.Interior.ColorIndex = 35
    Else
        row.Interior.ColorIndex = 37
    End If
Next
MsgBox (”Liczba Grup: ” & licznik)
End Sub

Tags: excel