Sub CF()
Dim ws As Worksheet
Dim i As Integer
Set ws = Sheets("Closed_Events")
i = 1
Cells.Select
Cells.FormatConditions.Delete
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$J4=""Yes""")
.Interior.Color = RGB(255, 255, 255)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$J2=""No""")
.Interior.Color = RGB(255, 0, 0)
.Font.Color = RGB(255, 255, 255)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$G2=""RTW""")
.Interior.Color = RGB(180, 198, 231)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=SEARCH(""Sickness"",$G2)")
.Interior.Color = RGB(198, 224, 180)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=SEARCH(""Mission"",$G2)")
.Interior.Color = RGB(255, 137, 137)
.Font.Color = RGB(0, 0, 0)
.Font.Bold = True
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$G2=""General""")
.Interior.Color = RGB(242, 242, 242)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$G2=""Staff Absence""")
.Interior.Color = RGB(248, 203, 173)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$G2=""Engineering""")
.Interior.Color = RGB(207, 175, 231)
.Font.Color = RGB(0, 0, 0)
End With
With Range("C2:O999").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=$G2=""Mandatory Training""")
.Interior.Color = RGB(255, 219, 105)
.Font.Color = RGB(0, 0, 0)
End With
Do Until i = 300
If ws.Range("I" & i).DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
msg = "I" & i & " -" & " Data has changed"
MsgBox msg
End If
i = i + 1
Loop
End Sub
All of the CF rules have "Stop If True" applied. Is there any way to turn this off by default, and add SiT to individual rules?
Bookmarks