将 Excel VBA 宏应用于所有突出显示的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21270917/
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
Apply Excel VBA macro to all highlighted cells
提问by user3221324
Thank you in advance, I am very new to excel VBA. This question may be very elementary, but I haven't found the answer in an extensive search. I originally recorded this macro and tweaked it with stuff I've found online. This macro works if you apply to one cell at a time (or if you drag across multiple rows, will work on the row of the top-left-most cell). Is there a way I can further tweak it to get my macro to apply the changes to the rows of all selected cells so that the user can make changes to rows in bulk?
在此先感谢您，我对 Excel VBA 非常陌生。这个问题可能非常初级，但我还没有在广泛的搜索中找到答案。我最初录制了这个宏，并用我在网上找到的东西对其进行了调整。如果您一次应用到一个单元格（或者如果您跨多行拖动，将在最左上角单元格的行上运行），则此宏有效。有没有办法可以进一步调整它以使我的宏将更改应用于所有选定单元格的行，以便用户可以批量更改行？
Range("A" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("A" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "5"
Range("B" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("B" & ActiveCell.Row).Select
End Sub
结束子
采纳答案by andy holaday
Maybe this is what you're after?
也许这就是你所追求的？
'Instead of this:
'Range("A" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
'Do this:
With Application.Intersect(Selection.EntireRow, Range("A:I")).Interior
'The range at hand is now all the cells in the rows of the selection,
' but limited to columns A:I.
'Notice we haven't actually modified the selection
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
'Range("A" & ActiveCell.Row).FormulaR1C1 = "5"
Application.Intersect(Selection.EntireRow, Range("A:A")).FormulaR1C1 = "5"
'Range("B" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Application.Intersect(Selection.EntireRow, Range("B:I")).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("B" & ActiveCell.Row).Select
Note: It is not necessary to .SELECTa range and then do something. You can usually just apply somethingto the range. What you're starting with is typical for macro recorder code, just know there is a cleaner way.
注意：没有必要先对.SELECT一个范围再做something。您通常可以只申请something范围。您开始使用的是宏记录器代码的典型特征，只要知道有一种更简洁的方法即可。
