为列中的特定单元格着色，而不必更改每个范围变量中的列引用

Question

下面的代码运行良好。然而，我觉得必须有一种更好的写作方式，才能更轻松、更快地完成这项工作。每周我都要进入VB并更改范围中的列引用，所以前一周会突出显示为红色。有没有办法输入一次列引用，这样所有其他的都会自动更新，或者更好的是，我可以在输入框中输入列引用？第一周是i，第二周是j，第三周是k，以此类推。谢谢！

    Option Explicit
Dim ws As Worksheet
Dim WSArray As Variant

Set WSArray = Sheets(Array("Baking HO", "Fresh Consol", "Moorebank", "Tamworth", "Canberra", "Fairbank", "Dandenong", "Carina", "Burleigh", "Townsville", _
           "Hobart", "Forestville", "Darwin", "Malaga", "Camellia", "EP-Total", "G144 Total", "G610 Total"))
For Each ws In WSArray
ws.Range("i6:i9,i17:i18,i22:i24,i28:i34,i39:i40,i46:i49,i51:i52,i55,i59:i62,i70:i74").Interior.Color = vbRed
Next
End Sub

Answer 1

将一个参数传递给您的sub以解决所需的周，并使用Range对象的Offset()方法，如下所示

Option Explicit
Sub ColorPreviuosWeek(col As Long)
    Dim ws As Worksheet
    Dim WSArray As Variant
    Set WSArray = Sheets(Array("Baking HO", "Fresh Consol", "Moorebank", "Tamworth", "Canberra", "Fairbank", "Dandenong", "Carina", "Burleigh", "Townsville", _
               "Hobart", "Forestville", "Darwin", "Malaga", "Camellia", "EP-Total", "G144 Total", "G610 Total"))
    For Each ws In WSArray
        ws.Range("i6:i9,i17:i18,i22:i24,i28:i34,i39:i40,i46:i49,i51:i52,i55,i59:i62,i70:i74").Offset(, col - 1).Interior.Color = vbRed '<--| offset must be reduced by one so as to have offset 1 referencing column "i"
    Next
End Sub

因此

ColorPreviuosWeek 2 '<-- 2 stands for "week2" 

将对列"J"进行着色

Answer 2

正在期待这样的事情。

VBA Excel 的交互式用户界面