Hello,
I have the below code to change cell color based on cell value. All works fine. If i change search date and there are less matches it leaves the cells colored. Is there away i can clear cell color before code runs. Also along with change cell color i want to change font color.
Private Sub Worksheet_Change(ByVal Target As Range)
Clear cell color before it runs below to match and change cell color??
Dim Cell As Range
For Each Cell In Range("B7:AB50")
If Cell.Value = "Pre Alert" Then
Cell.Interior.ColorIndex = 47
ElseIf Cell.Value = "Ok to Slot" Then
Cell.Interior.ColorIndex = 43
ElseIf Cell.Value = "Slotted" Then
Cell.Interior.ColorIndex = 7
ElseIf Cell.Value = "Delivery Pending" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "Delivery Confirmed" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Pick-Up Pending" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "Pick-Up Confirned" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Full on Site" Then
Cell.Interior.ColorIndex = 28
ElseIf Cell.Value = "Empty on Site" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Full in NFL Yard" Then
Cell.Interior.ColorIndex = 46
ElseIf Cell.Value = "Empty in NFL Yard" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Full at AQIS" Then
Cell.Interior.ColorIndex = 53 also want to change font color here to 6
ElseIf Cell.Value = "Empty at AQIS" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Job Completed" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Underbond Movement" Then
Cell.Interior.ColorIndex = 53
ElseIf Cell.Value = "Cancelled" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "On Power" Then
Cell.Interior.ColorIndex = 6
ElseIf Cell.Value = "Wharf to AQIS" Then
Cell.Interior.ColorIndex = 53
ElseIf Cell.Value = "Yes" Then
Cell.Interior.ColorIndex = 35
ElseIf Cell.Value = "No" Then
Cell.Interior.ColorIndex = 22
Else
End If
Next Cell
'Sort
Range("B6").CurrentRegion.Sort key1:=Range("T6"), order1:=xlAscending, Header:=xlYes
Range("B6").CurrentRegion.Sort key1:=Range("U6"), order1:=xlAscending, Header:=xlYes
End Sub
Screen Shot - Rows 13 to 17 Should = No Color in Column C
Thanks
Dale.
I have the below code to change cell color based on cell value. All works fine. If i change search date and there are less matches it leaves the cells colored. Is there away i can clear cell color before code runs. Also along with change cell color i want to change font color.
Private Sub Worksheet_Change(ByVal Target As Range)
Clear cell color before it runs below to match and change cell color??
Dim Cell As Range
For Each Cell In Range("B7:AB50")
If Cell.Value = "Pre Alert" Then
Cell.Interior.ColorIndex = 47
ElseIf Cell.Value = "Ok to Slot" Then
Cell.Interior.ColorIndex = 43
ElseIf Cell.Value = "Slotted" Then
Cell.Interior.ColorIndex = 7
ElseIf Cell.Value = "Delivery Pending" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "Delivery Confirmed" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Pick-Up Pending" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "Pick-Up Confirned" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Full on Site" Then
Cell.Interior.ColorIndex = 28
ElseIf Cell.Value = "Empty on Site" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Full in NFL Yard" Then
Cell.Interior.ColorIndex = 46
ElseIf Cell.Value = "Empty in NFL Yard" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Full at AQIS" Then
Cell.Interior.ColorIndex = 53 also want to change font color here to 6
ElseIf Cell.Value = "Empty at AQIS" Then
Cell.Interior.ColorIndex = 10
ElseIf Cell.Value = "Job Completed" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value = "Underbond Movement" Then
Cell.Interior.ColorIndex = 53
ElseIf Cell.Value = "Cancelled" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value = "On Power" Then
Cell.Interior.ColorIndex = 6
ElseIf Cell.Value = "Wharf to AQIS" Then
Cell.Interior.ColorIndex = 53
ElseIf Cell.Value = "Yes" Then
Cell.Interior.ColorIndex = 35
ElseIf Cell.Value = "No" Then
Cell.Interior.ColorIndex = 22
Else
End If
Next Cell
'Sort
Range("B6").CurrentRegion.Sort key1:=Range("T6"), order1:=xlAscending, Header:=xlYes
Range("B6").CurrentRegion.Sort key1:=Range("U6"), order1:=xlAscending, Header:=xlYes
End Sub
Screen Shot - Rows 13 to 17 Should = No Color in Column C
Thanks
Dale.