[已解决]菜鸟求助：下面代码如何修改？

alxixi520

请教：下面代码如何将数据改为由D3，D4开始？并且将“六，日”对应列指定不同颜色。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim yy As Integer, mm As Integer
    Dim rng As Range
    Set rng = Target.Cells(1, 1)
    If rng.Address = "$AB$1" And VBA.IsDate(rng) Then
        Me.Rows(3).ClearContents
        Me.Rows(4).ClearContents
        yy = Year(rng)
        mm = Month(rng)
        For i = 1 To VBA.DateSerial(yy, mm + 1, 1) - VBA.DateSerial(yy, mm, 1)
            Me.Cells(3, i) = i
            Me.Cells(4, i) = Application.WorksheetFunction.Choose(VBA.Weekday(DateSerial(yy, mm, i)), _
                "日", "一", "二", "三", "四", "五", "六")
        Next
    End If
End Sub

最佳答案

月排行榜 / 总排行榜

zjdh

2015-12-26 19:07

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim yy As Integer, mm As Integer
    Application.EnableEvents = False
    If Target.Address = "$AB$1" And VBA.IsDate(Target) Then
        Me.Rows(3).ClearContents
        Me.Rows(4).Clear
        yy = Year(Target)
        mm = Month(Target)
        For i = 1 To VBA.DateSerial(yy, mm + 1, 1) - VBA.DateSerial(yy, mm, 1)
            Me.Cells(3, i + 3) = i
            Me.Cells(4, i + 3) = Application.WorksheetFunction.Choose(VBA.Weekday(DateSerial(yy, mm, i)), _
                "日", "一", "二", "三", "四", "五", "六")
           If Cells(4, i + 3) = "六" Then Cells(4, i + 3).Interior.Color = 65535
           If Cells(4, i + 3) = "日" Then Cells(4, i + 3).Interior.Color = 225
        Next
    End If
    Application.EnableEvents = True
End Sub

跳转到最佳答案楼层

zjdh

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim yy As Integer, mm As Integer
    Application.EnableEvents = False
    If Target.Address = "$AB$1" And VBA.IsDate(Target) Then
        Me.Rows(3).ClearContents
        Me.Rows(4).Clear
        yy = Year(Target)
        mm = Month(Target)
        For i = 1 To VBA.DateSerial(yy, mm + 1, 1) - VBA.DateSerial(yy, mm, 1)
            Me.Cells(3, i + 3) = i
            Me.Cells(4, i + 3) = Application.WorksheetFunction.Choose(VBA.Weekday(DateSerial(yy, mm, i)), _
                "日", "一", "二", "三", "四", "五", "六")
           If Cells(4, i + 3) = "六" Then Cells(4, i + 3).Interior.Color = 65535
           If Cells(4, i + 3) = "日" Then Cells(4, i + 3).Interior.Color = 225
        Next
    End If
    Application.EnableEvents = True
End Sub

alxixi520

zjdh 发表于 2015-12-26 19:07
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim yy As Integer, mm ...

谢谢您好。差不多达到我需要的效果了。
           If Cells(4, i + 3) = "六" Then Cells(4, i + 3).Interior.ColorIndex = 27
           If Cells(4, i + 3) = "日" Then Cells(4, i + 3).Interior.ColorIndex = 3
           If Cells(4, i + 3) = "一" Then Cells(4, i + 3).Interior.ColorIndex = 4
           If Cells(4, i + 3) = "二" Then Cells(4, i + 3).Interior.ColorIndex = 4
           If Cells(4, i + 3) = "三" Then Cells(4, i + 3).Interior.ColorIndex = 4
           If Cells(4, i + 3) = "四" Then Cells(4, i + 3).Interior.ColorIndex = 4
           If Cells(4, i + 3) = "五" Then Cells(4, i + 3).Interior.ColorIndex = 4

这些有没有简化的方法？

zjdh

1.  If Cells(4, i + 3) = "六" Then Cells(4, i + 3).Interior.ColorIndex = 27
     If Cells(4, i + 3) = "日" Then Cells(4, i + 3).Interior.ColorIndex = 3
    If InStr("一二三四五", Cells(4, i + 3)) Then Cells(4, i + 3).Interior.ColorIndex = 4

2. Cells(4, i + 3).Interior.ColorIndex = 4
    If Cells(4, i + 3) = "六" Then Cells(4, i + 3).Interior.ColorIndex = 27
    If Cells(4, i + 3) = "日" Then Cells(4, i + 3).Interior.ColorIndex = 3