Hi我有以下代码,但它提示object_worksheet失败的错误范围。我不确定我做错了什么(我已经找到了使用record
宏的vba代码,并简单地复制和粘贴,除了我已经将所有selection
替换为ws.range(emptyrow)
,以指示范围到最后一个有值的单元格。此外,如果我将sub更改为sub highlightemptycell_change()
,并使用if
语句:"如果任何单元格被更改,那么执行以下操作",我将如何用vba语言编写它?
sub highlightemptycell()
Dim ws As Worksheet
Dim r As Range
Dim emptyrow As Long
Dim err As Range
Set ws = Worksheets("Master")
emptyrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1 '<<< safer....
ws.Range(emptyrow).FormatConditions(1).StopIfTrue = False
ws.Range(emptyrow).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISBLANK(ws.range(emptyrow)"
ws.Range(emptyrow).FormatConditions(ws.Range(emptyrow).FormatConditions.Count).SetFirstPriority
With ws.Range(emptyrow).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
我不确定你到底在做什么。特别是,我不确定这条线的意义
ws.Range(emptyrow).FormatConditions(1).StopIfTrue = False
尤其是在执行时单元格中没有条件格式时。
但是,如果对以下宏进行一点清理并使用正确的语法编写,那么它似乎可以做您的宏所做的事情
Option Explicit
Sub highlightemptycell()
Dim ws As Worksheet
Dim r As Range
Dim emptyrow As Long
Dim err As Range
Dim rEmptyRow As Range '<-- range object added to use below
Set ws = Worksheets("Master")
Set rEmptyRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(rowoffset:=1) '<<< safer....
With rEmptyRow.FormatConditions
If .Count > 0 Then .Item(1).StopIfTrue = False
.Add Type:=xlExpression, Formula1:= _
"=ISBLANK(" & rEmptyRow.Address & ")"
.Item(.Count).SetFirstPriority
With .Item(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
End With
End Sub