VBA—使用VBA的带有动态公式的条件格式

Question

我正在制作一个项目规划工作表，并编写一个函数来添加一个团队成员，该团队成员插入一些分组单元格，用于汇总每周的小时数。

新单元格应该具有条件格式，以显示实际工作总小时数(以蓝色显示)何时超过计划工作小时数(列M)。

例子管理规则框

我有一些麻烦使用的范围，我定义在公式1:当我运行程序时，它确实定义了条件格式，但是在"管理规则"中显示的公式显示区域的名称，而不是我试图格式化的单元格。

任何和所有的帮助是非常感谢!

我已经定义了要格式化的单元格范围，但是当使用MyRange. formatconditions . add Type:=xlExpression, Formula1:="=sum(MyRange)>FmtHrs">

时，它似乎不能转化为实际的单元格选择

Sub Button1_Click()
Dim iCol As Long
Dim WeekCount As Long
Dim LastCol As Long
Dim RowCount As Integer
Dim FmtRow As Long
Set ws = Sheet1


'This sections adds new staff member info'

'This sections adds weeks for new staff memeber'
WeekCount = Range("B3").Value  'number of columns to add (weeks of project in B3)'
LastCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column  'position of new columns at last column with data'
Columns(LastCol + 1).Insert Shift:=xlToRight
For i = 1 To WeekCount  'inserts columns for each week of project'
Columns(LastCol + 1).Insert Shift:=xlToRight
Next i
'insterns column for new staff and adds labels'
LastCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column + WeekCount + 1
ActiveSheet.Cells(3, LastCol).Value = "Role"
ActiveSheet.Cells(4, LastCol).Value = "Name"
ActiveSheet.Cells(5, LastCol).Value = "Rate"
'Groups New Columns
LastCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
Range(Cells(1, LastCol - WeekCount), Cells(1, LastCol - 1)).Columns.Group
ActiveSheet.Outline.ShowLevels ColumnLevels:=1 'collapses all groups
'Adds Conditional Formats to New Rows
RowCount = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1
FmtRow = RowCount
Dim FmtHrs As Long
Dim MyRange As Range
Dim condition1 As FormatCondition

For i = RowCount - 1 To 6 Step -1
FmtHrs = ActiveSheet.Cells(FmtRow, LastCol)
Set MyRange = Range(Cells(FmtRow, LastCol - WeekCount), Cells(FmtRow, LastCol - 1))

MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=sum(MyRange)>FmtHrs"
MyRange.Interior.Color = RGB(128, 100, 250)
FmtRow = FmtRow - 1
Next i

End Sub

Answer 1

将"=sum(MyRange)>FmtHrs"作为公式写入条件格式。在Excel中，MyRange没有意义，这是一个VBA变量。您需要的是将范围地址写入公式。

一个建议:不要直接写公式，总是使用一个中间变量，这样更容易检查是否有东西不像预期的那样工作

Dim formula As String
formula = "=sum(" & MyRange.Address & ")>FmtHrs"
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:=formula