我不太擅长 VBA,甚至不会称自己为“程序员”。我尝试使用 Excel VBA 将单元格的颜色更改为黄色,如果单元格的值是今天的日期,则使用黑色粗体文本。数据输入如下:“06/20/22 8:00”。因此,我尝试应用 int() 函数来删除不是日期的信息(例如时间)。当我运行代码时,出现错误“类型不匹配”,但即使解决了该错误,我也不确定此代码是否有效。该代码可以根据其他条件更改单元格颜色。唯一不起作用的部分是我最后尝试让代码根据今天的日期更改单元格值。感谢您的所有帮助。
Sub Color_Cell_Condition()
Dim MyCell As Range
Dim StatValue As String
Dim DateValue As Date
Dim SubjectRange As Range
Dim EventTypeRange As Range
Dim EventDateRange As Range
Set SubjectRange = Range("DSubject")
Set EventTypeRange = Range("DEventType")
Set EventDateRange = Range("DDateRange")
For Each MyCell In SubjectRange
StatValue = MyCell.Value
Select Case StatValue
Case "Court"
MyCell.Interior.Color = RGB(192, 0, 0)
Case "Deadline"
MyCell.Interior.Color = RGB(32, 55, 100)
Case "Appointment"
MyCell.Interior.Color = RGB(55, 86, 35)
End Select
Next MyCell
For Each MyCell In EventTypeRange
StatValue = MyCell.Value
Select Case StatValue
Case "Joint Scheduling Report"
MyCell.Interior.Color = RGB(169, 208, 142)
MyCell.Font.Color = RGB(0, 0, 0)
Case "Joint Pretrial Stipulation"
MyCell.Interior.Color = RGB(255, 102, 0)
MyCell.Font.Color = RGB(255, 255, 0)
MyCell.Font.Bold = True
Case "Statement of Claim"
MyCell.Interior.Color = RGB(165, 165, 165)
MyCell.Font.Color = RGB(0, 0, 0)
Case "Response to Motion"
MyCell.Interior.Color = RGB(255, 0, 0)
MyCell.Font.Color = RGB(255, 255, 0)
MyCell.Font.Bold = True
End Select
Next MyCell
For Each MyCell In EventDateRange
DateValue = Int(MyCell.Value)
Select Case DateValue
Case Date
MyCell.Interior.Color = RGB(255, 255, 0)
MyCell.Font.Color = RGB(0, 0, 0)
MyCell.Font.Bold = True
End Select
Next MyCell
End Sub
答案1
在对问题添加了评论后,我意识到你把这个问题复杂化了。如果你只想根据今天是否为单元格着色,只需使用此
Dim myCell As Range
For Each myCell In .Range("A1:Z99")
If isdate(myCell) then
If format(myCell.Value, "mm/dd/yyyy") = format(Date, "mm/dd/yyyy") Then
myCell.Interior.Color = vbYellow
myCell.Font.Bold = True
End If
End if
Next
End With
您应该将其放在计时器上,或将其放在事件中。
答案2
您可以使用以下公式的条件格式:
=ROUNDDOWN(A1,0) = TODAY()