时间戳

首页 > 解决方案 > VBA - 如果列包含文本并且另一列是工作日，则应用条件格式

excel - VBA - 如果列包含文本并且另一列是工作日，则应用条件格式

问题描述

Excel文件

所以这是我的文件。我一周中的所有日子都在一张纸上。

我想要做的是在 1ST、CUCA、SERVICE ADMIN、2ND、CHAT 的行中使用条件格式。我已经想通了，但现在我希望这仅适用于周一至周四，然后适用于周五、周六、周日的其他规则。

所以这是我目前拥有的代码

Sub SetFormulasFormat()
  
Application.ScreenUpdating = False
Dim cl As Range
With ActiveSheet

        
        
    For Each cl In Application.Intersect(.Columns("E"), .UsedRange)
      
        If UCase(cl.Value) = "1ST" And Weekday(cl.Offset(, -2), vbMonday) < 6 Then
            .Range("F" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1" 'apply CF rule to 1 single cell in same row
            .Range("F" & cl.Row).FormatConditions(1).Interior.Color = vbRed
            
            .Range("G" & cl.Row & ",H" & cl.Row & ",Y" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=3" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("G" & cl.Row & ",H" & cl.Row & ",Y" & cl.Row).FormatConditions(1).Interior.Color = vbRed
            
            .Range("I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=4" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions(1).Interior.Color = vbRed
 
            .Range("I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=7" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row).FormatConditions(2).Interior.Color = RGB(128, 0, 128)
            
            .Range("J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=5" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row).FormatConditions(3).Interior.Color = RGB(255, 140, 0)
            
            .Range("Z" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=2" 'apply CF rule to 1 single cell in same row
            .Range("Z" & cl.Row).FormatConditions(1).Interior.Color = vbRed
            
        End If
        If UCase(cl.Value) = "CUCA" Then
            
            .Range("G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions(1).Interior.Color = vbRed

        End If
        If UCase(cl.Value) = "SERVICE ADMIN" Then
            
            .Range("G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions(1).Interior.Color = vbRed
        End If
        
        If UCase(cl.Value) = "2ND" Then
            
            .Range("F" & cl.Row & ",G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("F" & cl.Row & ",G" & cl.Row & ",H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row & ",X" & cl.Row).FormatConditions(1).Interior.Color = vbRed
        
         End If
         
        If UCase(cl.Value) = "CHAT" Then
            
            .Range("H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1" 'apply CF rule to 2 different cells in same row (Separate each cell with , like G2,H2...)
            .Range("H" & cl.Row & ",I" & cl.Row & ",J" & cl.Row & ",K" & cl.Row & ",L" & cl.Row & ",M" & cl.Row & ",N" & cl.Row & ",O" & cl.Row & ",P" & cl.Row & ",Q" & cl.Row & ",R" & cl.Row & ",S" & cl.Row & ",T" & cl.Row & ",U" & cl.Row & ",V" & cl.Row & ",W" & cl.Row).FormatConditions(1).Interior.Color = vbRed
                                     
        
        End If
        
    Next cl
    
End With
Application.ScreenUpdating = True
End Sub

标签： excelvbadate

解决方案

您可以使用Weekday来确定星期几并创建两个IF语句，一个用于 <5，另一个用于 >4。周五、周六和周日分别是第 5、6 和 7 天。您可以参考该函数来确定您想要的日期、C 列、今天的日期等。

推荐阅读