使用 VBA 选择和突出显示 Excel 行
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/10923053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA to Select and Highlight Excel Rows
提问by Dan
How can I tell Excel to highlight rows by their row number. For instance, let's say I wanted row 6, 10, 150, 201 highlighted. Thanks.
如何告诉 Excel 按行号突出显示行。例如,假设我想要突出显示第 6、10、150、201 行。谢谢。
回答by Tim Williams
As an alternative to Motes' answer, you can use conditional formatting.
作为 Motes 答案的替代方案,您可以使用条件格式。
Eg: select A1:J500, Conditional formatting >> New rule >> Use a formula...
例如:选择 A1:J500,条件格式 >> 新规则 >> 使用公式...
For the formula enter: =OR(ROW()=6, ROW()=10, ROW()=150, ROW()=201)
对于公式输入: =OR(ROW()=6, ROW()=10, ROW()=150, ROW()=201)
回答by Siddharth Rout
Here is another one based on Mote's .EntireRow.Interior.ColorIndex
这是另一个基于 Mote 的 .EntireRow.Interior.ColorIndex
This one doesn't restrict you to enter the row numbers but gives the user the flexibility to choose the rows at runtime.
这不限制您输入行号,而是让用户可以在运行时灵活地选择行。
Option Explicit
Sub Sample()
Dim Ret As Range
On Error Resume Next
Set Ret = Application.InputBox("Please select the rows that you would like to color", "Color Rows", Type:=8)
On Error GoTo 0
If Not Ret Is Nothing Then Ret.EntireRow.Interior.ColorIndex = 6
End Sub
FOLLOWUP
跟进
Is there a way to write the macro to read the row numbers from a list and highlight the rows?
有没有办法编写宏来从列表中读取行号并突出显示行?
Yes there is a way. Let's say the list in Cell A1 to A10 then you can use this code
是的,有办法。假设单元格 A1 到 A10 中的列表然后您可以使用此代码
Option Explicit
Sub Sample()
Dim i As Long, sh As Worksheet
On Error GoTo Whoa
Application.ScreenUpdating = False
'~~> Set this to the sheet where the rows need to be colored
Set sh = Sheets("Sheet2")
'~~> Change Sheet1 to the sheet which has the list
With Sheets("Sheet1")
For i = 1 To 10
If Not Len(Trim(.Range("A" & i).Value)) = 0 And _
IsNumeric(.Range("A" & i).Value) Then _
sh.Rows(.Range("A" & i).Value).Interior.ColorIndex = 3 '<~~ Red
Next i
End With
LetsContinue:
Application.ScreenUpdating = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
回答by Steve Konves
For basic VBA code, you can always start recording a macro, perform the action, stop recording, look at what code was generated, and then clean that up to do what you want. For example, recording the action of highlighting a row (setting the value of Interior.Color) gives you:
对于基本的 VBA 代码,您始终可以开始录制宏、执行操作、停止录制、查看生成的代码,然后对其进行清理以执行您想要的操作。例如,记录高亮一行的动作(设置 Interior.Color 的值)给你:
Rows("13:13").Select
Range("C13").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
The selection commands and extraneous Interior properties can be removed giving you:
可以删除选择命令和无关的内部属性,为您提供:
Rows("13:13").Interior.Color = 65535
Adding in the row multi-select:
在行中添加多选:
Rows("6:6,10:10,150:150,201:201").Interior.Color = 65535
Summary:
概括:
- Record macro
- View Excel's version
- Use/Edit what code you need
- 录制宏
- 查看 Excel 的版本
- 使用/编辑您需要的代码
回答by Motes
objWB.Cells(rowNum,201).EntireRow.Interior.ColorIndex = 6
etc
等等
回答by Kelsius
Update: Didn't realize the date on this, but thought I'd add this in since it was relevant to the chosen answer.
更新:没有意识到这个日期,但我想我会添加这个,因为它与选择的答案相关。
In addition to Siddharth Rout's answer, since I don't have enough rep to comment yet, you can dynamically figure out how many rows there are in your worksheet with these two lines. xlCellTypeConstants
could be changed to another XlCellType constant that you need, and the range can always be changed to accommodate to your spreadsheet.
除了 Siddharth Rout 的回答之外,由于我还没有足够的代表进行评论,因此您可以使用这两行动态计算工作表中有多少行。 xlCellTypeConstants
可以更改为您需要的另一个 XlCellType 常量,并且始终可以更改范围以适应您的电子表格。
Dim numRows As Integer
numRows = Range("A2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeConstants).Cells.Count
回答by Ray
Sorry if it is not as concise or elegant as other answers, but it gets the job done. When I was writing code for my own application I needed to loop through my code. Also, instead of highlighting the entire row, I only needed to highlight a portion of the rows.
对不起,如果它不像其他答案那样简洁或优雅,但它可以完成工作。当我为自己的应用程序编写代码时,我需要遍历我的代码。此外,我只需要突出显示行的一部分,而不是突出显示整行。
Sub Highlight()
Dim ThisWB As Workbook
Dim ThisWS As Worksheet
Dim rows(0 To 3) As Integer
Dim test As String
Set ThisWB = ActiveWorkbook
Set ThisWS = ThisWB.Sheets("Sheet1")
rows(0) = 6
rows(1) = 10
rows(2) = 150
rows(3) = 201
For i = 0 To 3
test = "A" & rows(i) & ":H" & rows(i)
ThisWS.Range(test).Interior.ColorIndex = 15
Next i
End Sub
回答by user6683046
You might be able to achieve the same thing using conditional formatting
您也许可以使用条件格式来实现相同的目的
- put list of values in a column (I use a separate tab and give the list a name)
- under conditional formatting - New Rule - "use a formula to determine with cells to format"
- read this article http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/
- the rule uses vlookup in the formula- =$A2=VLOOKUP($A2,list,1,FALSE)
- 将值列表放在一列中(我使用单独的选项卡并为列表命名)
- 在条件格式下 - 新规则 - “使用公式来确定要格式化的单元格”
- 阅读这篇文章http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/
- 该规则在公式中使用 vlookup - =$A2=VLOOKUP($A2,list,1,FALSE)