Change Cell Font Color and clear Cell Color

D

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
59
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
1587460265783.png

Thanks

Dale.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
How about
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cell As Range
   
   Range("B7:AB50").Interior.ColorIndex = xlNone
   For Each Cell In Range("B7:AB50")
      Select Case Cell.Value
         Case "Pre Alert"
            Cell.Interior.ColorIndex = 47
         Case "Ok to Slot"
            Cell.Interior.ColorIndex = 43
         Case "Slotted"
            Cell.Interior.ColorIndex = 7
         Case "Delivery Pending", "Pick-Up Pending", "Cancelled"
            Cell.Interior.ColorIndex = 3
         Case "Delivery Confirmed", "Pick-Up Confirned", "Job Completed"
            Cell.Interior.ColorIndex = 4
         Case "Full on Site"
            Cell.Interior.ColorIndex = 28
         Case "Empty on Site", "Empty in NFL Yard", "Empty at AQIS"
            Cell.Interior.ColorIndex = 10
         Case "Full in NFL Yard"
            Cell.Interior.ColorIndex = 46
         Case "Full at AQIS"
            Cell.Interior.ColorIndex = 53
            Cell.Font.ColorIndex = 6
         Case "Underbond Movement", "Wharf to AQIS"
            Cell.Interior.ColorIndex = 53
         Case "On Power"
            Cell.Interior.ColorIndex = 6
         Case "Yes"
            Cell.Interior.ColorIndex = 35
         Case "No"
            Cell.Interior.ColorIndex = 22
      End Select
   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
 
Upvote 0
Fluff said:
Dim Cell As Range Range("B7:AB50").Interior.ColorIndex = xlNone For Each Cell In Range("B7:AB50") Select Case Cell.Value Case "Pre Alert" Cell.Interior.ColorIndex = 47 Case "Ok to Slot" Cell.Interior.ColorIndex = 43 Case "Slotted" Cell.Interior.ColorIndex = 7 Case "Delivery Pending", "Pick-Up Pending", "Cancelled" Cell.Interior.ColorIndex = 3 Case "Delivery Confirmed", "Pick-Up Confirned", "Job Completed" Cell.Interior.ColorIndex = 4 Case "Full on Site" Cell.Interior.ColorIndex = 28 Case "Empty on Site", "Empty in NFL Yard", "Empty at AQIS" Cell.Interior.ColorIndex = 10 Case "Full in NFL Yard" Cell.Interior.ColorIndex = 46 Case "Full at AQIS" Cell.Interior.ColorIndex = 53 Cell.Font.ColorIndex = 6 Case "Underbond Movement", "Wharf to AQIS" Cell.Interior.ColorIndex = 53 Case "On Power" Cell.Interior.ColorIndex = 6 Case "Yes" Cell.Interior.ColorIndex = 35 Case "No" Cell.Interior.ColorIndex = 22 End Select Next Cell
Click to expand...
Fluff said:
How about
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cell As Range
  
   Range("B7:AB50").Interior.ColorIndex = xlNone
   For Each Cell In Range("B7:AB50")
      Select Case Cell.Value
         Case "Pre Alert"
            Cell.Interior.ColorIndex = 47
         Case "Ok to Slot"
            Cell.Interior.ColorIndex = 43
         Case "Slotted"
            Cell.Interior.ColorIndex = 7
         Case "Delivery Pending", "Pick-Up Pending", "Cancelled"
            Cell.Interior.ColorIndex = 3
         Case "Delivery Confirmed", "Pick-Up Confirned", "Job Completed"
            Cell.Interior.ColorIndex = 4
         Case "Full on Site"
            Cell.Interior.ColorIndex = 28
         Case "Empty on Site", "Empty in NFL Yard", "Empty at AQIS"
            Cell.Interior.ColorIndex = 10
         Case "Full in NFL Yard"
            Cell.Interior.ColorIndex = 46
         Case "Full at AQIS"
            Cell.Interior.ColorIndex = 53
            Cell.Font.ColorIndex = 6
         Case "Underbond Movement", "Wharf to AQIS"
            Cell.Interior.ColorIndex = 53
         Case "On Power"
            Cell.Interior.ColorIndex = 6
         Case "Yes"
            Cell.Interior.ColorIndex = 35
         Case "No"
            Cell.Interior.ColorIndex = 22
      End Select
   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
Click to expand...

Thank you so much. Now I can spend my night fine tuning.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Is There a More Efficient Way to Code This?
Replies
2
Views
352
offthelip
O
D
  • Question
Change Button Object Background Color & Font Color
Replies
3
Views
790
Fluff
Fluff
K
  • Question
Do not change a cell value when different value appear.
Replies
5
Views
326
footoo
F
J
  • Solved
Date Issue Between Users
2
Replies
18
Views
805
JBDB
J
A
  • Question
Unable To Change Font Color Of A Cell
Replies
1
Views
379
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top