Barrakooda
Board Regular
- Joined
- Feb 3, 2012
- Messages
- 75
Hi all
Does anyone know how to write this better, worked ok yesterday. Today it doesn't conditional format G2 down, index's fine.
Does anyone know how to write this better, worked ok yesterday. Today it doesn't conditional format G2 down, index's fine.
Code:
' References job numbers to actual backlogs to give desrciptions then conditional formats them based on backlog status
Dim my_range As Range
With ActiveSheet
Range("E2", Range("E" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),6)"
With ActiveSheet
Range("F2", Range("F" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),14)"
Selection.FormatConditions.Add Type:=xlTextString, String:="Job Ready", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Not Ready", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Parts Not Ordered", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Last edited by a moderator: