title: Changing cell color when value is in cell
body: <p>In my main sheet, I have a column (column C) where a long 16 digit code will be entered. The next column (Column D) derives the last 6 digits of that code with the formula "=MID(cell in column C,16,6". If the last 6 digits of the longer code equals any of the codes listed out in my <code>Case</code> statement in the code, the corresponding cell in column F should turn red to indicate to the user that the cell in column F needs a code. Once the F column cell turns red, the user is able to click on that cell in the F column and it takes the user to another list of codes. The user can double-click on any code and it will populate the F column back in the main sheet. </p>
  
  <p>As of now, when a code populates the cell in the F column, it turns to a no-fill background (as I would like) <em>but</em>, when I either enter any other data in any cell in the <strong>same row</strong>, the cell in the F column turns back to being red with the code still in the cell. I need that cell in the F column to stay a no-fill background after having data entered in it, unless the code is deleted from the cell, then it can turn back to red to indicate to the user that this cell needs a value. I just can't have the cell turning red when there is a code still inside of it. I feel as though I am somewhat close but I don't know the VBA syntax well enough to get this functionality to work. Any suggestions would be greatly appreciated. </p>
  
  <p>Thanks in advance. 
      I will post the code to the main sheet/form below: </p>
  
  <pre><code>Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  
        Dim c As Range: Set c = Range("D7:D446")
        Dim d As Range: Set d = Range("F7:F446")
  
   For Each c In c.Cells
            Select Case c.Value
                Case "1000GP", "1000MM", "19FEST", "20IEDU", "20ONLC", "20PART", "20PRDV", "20SPPR", "22DANC", "22LFLC", "22MEDA", "530CCH", "60POUBL", "74GA01", "74GA17", "74GA99", "78REDV"
                    Cells(c.Row, "F").Interior.ColorIndex = 3
                Case Else
                   Cells(c.Row, "F").Interior.ColorIndex = 0
            End Select
        Next c
        If Not Application.Intersect(d, Range(Target.Address)) _
             Is Nothing Then
        Target.Interior.ColorIndex = 0
  
        End If
  
  End Sub
  </code></pre>