创建一个分隔符

huangapple go评论64阅读模式
英文:

Create a separator

问题

我想要在每次执行此代码后创建一个有颜色的行,以在每次执行之间或者为最后一节着色。

在下面的代码中,如何创建一个有颜色的空行以在操作结束时?

Sub CopyHighlightedTransactions()
Dim mycell As Range
Dim myrange As Range
Dim lastrow As Long
Set myrange = Worksheets("sheet2").Range("a2:a1000")
myrange.Interior.Pattern = xlNone

Dim mc As Range
Dim my As Range
Set my = Worksheets("sheet4").Range("a2:a1000")
my.Interior.Pattern = xlNone
For Each mc In my
  For i = 1 To 10
    If mc.Value = Application.WorksheetFunction.Large(my, i) Then
      mc.Interior.ColorIndex = 4
      mc.Resize(1, 16).Cut Destination:= _
            Worksheets("sheet5").Range("a1").Offset(Worksheets("sheet5").Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
    End If
  Next
Next mc

Worksheets("sheet5").Columns.AutoFit
End Sub

我想要在每次执行此代码后创建一个有颜色的行。

英文:

I want to create a colored line after each execution of this code between each execution or to color the last section.
In the code below, how can you create a colored blank row at the end of the operation?

Sub CopyHighlightedTransactions()
Dim mycell As Range
Dim myrange As Range
Dim lastrow As Long
Set myrange = Worksheets("sheet2").Range("a2:a1000")
myrange.Interior.Pattern = xlnon

Dim mc As Range
Dim my As Range
Set my = Worksheets("sheet4").Range("a2:a1000")
my.Interior.Pattern = xlnon
For Each mc In my
  For i = 1 To 10
    If mc.Value = Application.WorksheetFunction.Large(my, i) Then
      mc.Interior.ColorIndex = 4
      mc.Resize(1, 16).Cut Destination:= _
            Worksheets("sheet5").Range("a1").Offset(Worksheets("sheet5").Rows.Count - 1, 0).End(xlUp).Offset(1, 0)

    End If

  Next
Next mc

Worksheets("sheet5").Columns.AutoFit
End Sub

I want to create a colored line after each execution of this code between each execution or to color the last section.

答案1

得分: 0

请尝试调整后的代码。它调用markLast函数,它会将最后一行后面的空行的内部部分涂黑:

Sub CopyHighlightedTransactions()
    Dim mycell As Range, myrange As Range, lastrow As Long
    Dim ws2 As Worksheet, ws4 As Worksheet, ws5 As Worksheet

    Set ws2 = Worksheets("sheet2")
    Set ws4 = Worksheets("sheet4")
    Set ws5 = Worksheets("sheet5")

    Set myrange = ws2.Range("a2:a1000")
    myrange.Interior.Pattern = xlNone ' 假设您不需要任何填充图案...

    Dim mc As Range, my As Range
    Set my = ws4.Range("a2:a1000")
    my.Interior.Pattern = xlNone

    For Each mc In my
        For i = 1 To 10
            If mc.Value = Application.WorksheetFunction.Large(my, i) Then
                mc.Interior.ColorIndex = 4
                mc.Resize(1, 16).Cut Destination:= _
                    ws5.Range("a1").Offset(ws5.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
            End If
        Next
    Next mc

    ws5.Columns.AutoFit

    ' 将最后一行后的空行的内部涂黑:
    markLast ws5
End Sub

Sub markLast(ws As Worksheet)
    With ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1)
        .Value = "."
        .EntireRow.Interior.Color = vbBlack
    End With
End Sub

如果xlnon不是自定义填充图案,它应该是xlNone,如果您需要清除填充图案。在这种情况下,请在模块的顶部(在声明区域)写上Option Explicit。这将强制您声明所有变量,并会在有拼写错误时提醒您

另外,请注意代码缩进。如果没有缩进,如果代码变得复杂,将很难理解和调试。

英文:

Please, try the adapted code. It calls markLast which colors in black the interior of the empty row after the last one:

Sub CopyHighlightedTransactions()
 Dim mycell As Range, myrange As Range, lastrow As Long
 Dim ws2 As Worksheet, ws4 As Worksheet, ws5 As Worksheet

 Set ws2 = Worksheets("sheet2")
 Set ws4 = Worksheets("sheet4")
 Set ws5 = Worksheets("sheet5")

 Set myrange = ws2.Range("a2:a1000")
 myrange.Interior.Pattern = xlNone 'supposing that you do not need any pattern...


 Dim mc As Range, my As Range
 Set my = ws4.Range("a2:a1000")
 my.Interior.Pattern = xlNone 

 For Each mc In my
    For i = 1 To 10
        If mc.Value = Application.WorksheetFunction.Large(my, i) Then
          mc.Interior.ColorIndex = 4
          mc.Resize(1, 16).Cut Destination:= _
                ws5.Range("a1").Offset(ws5.rows.count - 1, 0).End(xlUp).Offset(1, 0)
        End If
    Next
 Next mc

 ws5.Columns.AutoFit

 'color the interiror of the empty row after the last one in black:
 markLast ws5
End Sub

Sub markLast(ws As Worksheet)
     With ws.Range("A" & ws.rows.count).End(xlUp).Offset(1)
        .Value = "."
        .EntireRow.Interior.Color = vbBlack
     End With
End Sub

If xlnon is not a custom pattern, it should be xlNone if you need to clear the pattern. To be none... In such a case, please write Option Explicit on top of the module (in the declarations area). It will force you to declare all variables and it will warn you in case of a typo.

And take care of the code indentation. Without it, if the code becomes complex it would be very difficult to understand and debug it.

huangapple
  • 本文由 发表于 2023年4月11日 02:16:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/75979619.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定