英文:
Vba coding with if statement
问题
我正在尝试对P列中的一定范围进行格式化/填充，当L列单元格中有日期格式并且O列单元格中存在单词"Ready"时，将其设置为特定颜色。我尝试了以下公式，但目前还没有成功。请参考以下代码：
Dim ws as worksheet
Set ws = days_ready
If ws.Range("L3:L76").NumberFormat = "mm/dd/yyyy" And ws.Range("O3:O76").Value = "Ready" Then
ws.Range("P3:P76").FormatConditions.Interior.Color = 2162853
End if
任何帮助/建议将不胜感激。谢谢。
我尝试使用VBA代码的If语句，但出现了"方法'range'对象'_Worksheet'失败"的错误。
英文:
I am trying to format/fill a range in column P to a certain color when there is date format in column L cells and this word (Ready) is present in cells of column O. I tried this formula but so far, it hasn’t worked. See below.
Dim ws as worksheet
Set ws = days_ready
If ws.range("L3:L76").NumberFormat = "mm/did/yyyy" And ws.Range("O3:O76").Value = "Ready" Then
ws.Range("P3:P76").FormatConditions.Interior.Color = 2162853
End if
Any help/suggestion will be appreciated. Thank you.
I tried a If statement with vba coding but I got “method ‘range’ of object’_Worksheet’ failed” error.
答案1
得分: 1
请尝试以下代码，注意我已将NumberFormat测试更改为"mm/ddd/yyyy"，我假设你原始的帖子中有一个拼写错误。
Dim ws As Worksheet
Dim rWork As Range, r As Range
Set ws = days_ready
Set rWork = ws.Range("L3:L76")
For Each r In rWork
If r.NumberFormat = "mm/ddd/yyyy" And ws.Range("O" & r.Row).Value = "Ready" Then
ws.Range("P" & r.Row).FormatConditions.Interior.Color = 2162853
End If
Next r
英文:
Try this, note I have changed the NumberFormat test to "mm/ddd/yyyy", I assume your original post had a typo.
Dim ws As Worksheet
Dim rWork As Range, r As Range
Set ws = days_ready
Set rWork = ws.Range("L3:L76")
For Each r In rWork
If r.NumberFormat = "mm/ddd/yyyy" And ws.Range("O" & r.Row).Value = "Ready" Then
ws.Range("P" & r.Row).FormatConditions.Interior.Color = 2162853
End If
Next r
答案2
得分: 0
你可以尝试这样做：
Public Sub 填充单元格()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("days_ready")
Dim r As Range
Dim v As String
Dim i As Integer
Set r = Range("O3:O5")
For Each cell In r
v = cell.Value
i = cell.Row
If (v = "Ready") Then
ws.Cells(i, 16).Interior.ColorIndex = 6
End If
Next
End Sub
这段代码的作用是在名为"days_ready"的工作表中，将O3到O5范围内的单元格进行填充。如果单元格的值为"Ready"，则将对应行的第16列单元格的背景颜色设置为6。
英文:
You may try something like this:
Public Sub FillCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("days_ready")
Dim r As Range
Dim v As String
Dim i As Integer
Set r = Range("O3:O5")
For Each cell In r
v = cell.Value
i = cell.Row
If (v = "Ready") Then
ws.Cells(i, 16).Interior.ColorIndex = 6
End If
Next
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论