我正在尝试将颜色填充代码添加到worksheet_change私有子中以突出显示日期值已更改的单元格 . 在日期列(第1列或第A列)中进行任何更改后,此宏还更新了引用的数据库 . 这是代码:
Private Sub Worksheet_Change(ByVal Target As Range)
If strChk = "Don't Change Yet" Then Exit Sub
Dim r As Integer, c As Integer
r = Target.Row
c = Target.Column
If openMode = True Then Exit Sub
If Trim(Cells(Target.Row, 1)) = "" Then Exit Sub
If r = 2 Then Exit Sub
If r = 3 Then Exit Sub
If Not c = 1 Then Exit Su
If Not IsNumeric(Cells(Target.Row, 2)) Then
MsgBox "no orca number"
Exit Sub
End If
If Not IsDate(Cells(Target.Row, 1)) Then
MsgBox "Target date is invalid! Weird, right?"
Exit Sub
End If
Dim strsqla As String
strsqla = "select target_date, orca, cow from orca " & _
"where orca_id = " & Cells(Target.Row, 2)
Dim adoSQLcon As ADODB.Connection
Set adoSQLcon = New ADODB.Connection
adoSQLcon.Open "Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=ENV_AC_Nording_dw_DEV;Data Source=S0662K806"
Dim adoSQLRst As ADODB.Recordset
Set adoSQLRst = New ADODB.Recordset
adoSQLRst.Open strsqla, adoSQLcon, adOpenStatic, adLockOptimistic
If adoSQLRst.RecordCount > 0 Then
If IsDate(Target.Value) Then
adoSQLRst!target_date = Cells(Target.Row, 1).Value
MsgBox "Date Updated"
End If
If Not Cells(Target.Row, 4).Value = "" Then
adoSQLRst!ORCA = Cells(Target.Row, 4).Value
MsgBox "ORCA Description Updated"
End If
If Not Cells(Target.Row, 8).Value = "" Then
adoSQLRst!COW = Cells(Target.Row, 8).Value
MsgBox "CoW Devices Updated"
End If
adoSQLRst.Update
End If
adoSQLRst.Close
Set adoSQLRst = Nothing
adoSQLcon.Close
Set adoSQLcon = Nothing
Cells(Target.Row, 1).Interior.Color = RGB(255, 255, 0)
End Sub
所以我添加的代码是“Cells(Target.Row,1).Interior.Color = RGB(255,255,0)” . 所以我想在改变之后突出显示细胞 . 但它最终导致运行时错误“1004”:应用程序定义或对象定义错误 . 所以我想知道是否有人可以帮助我 . 我尝试过从网站上可以找到的所有不同类型的颜色填充代码但是所有这些代码都产生了这个错误 . 非常感谢您提供的任何帮助 .
2 回答
首先替换:
同
可能还有其他问题 .
尝试使用
Cells(Target.Range.Row, 1).Interior.Color = RGB(255, 255, 0)
我不确定它是否会起作用,但这就是我在自己的脚本中使用它的方式 .