英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论