Changing cell color when value is in cell

Question

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 Case 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.

As of now, when a code populates the cell in the F column, it turns to a no-fill background (as I would like) but, when I either enter any other data in any cell in the same row, 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.

Thanks in advance. I will post the code to the main sheet/form below:

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

Your code only activates if something is changed in column D not column F. — Plagon, Jun 22 '17 at 16:19
@UGP Thanks so much for your answer. That worked. When I select a code, it now populates the cell in the F column and turns the cell back to a no-fill color. However, when I delete that code from the cell in the F column, I would like the F cell to turn back to a red color. As of now, it just stays a no-fill background color upon deletion. Would you happen to know how I could go about doing that? Thanks again! Much appreciated. — anve, Jun 22 '17 at 16:57
I added a short Version you might wanna check out. Both Versions do the same no and will adapt if the value in F is "" — Plagon, Jun 22 '17 at 17:25
@UGP the very first snip-it of code in your first answer seems to work the best. However, after the cell in the F column turns white, when I add another code in the row below D column the cell in the F column turns back to red. So, if I added a code into cell F8 and it turns white, when I go to add a different code in cell D9, cell F8 will turn back to red. I would like for it to stay white unless the user deletes the code in cell F8, then and only then would I like it to turn back to red. I'll update my questions code. Thanks for the help so far! — anve, Jun 22 '17 at 18:23
That happens should only happen in the version you startet with. you check every cell in column d after any change on the worksheets and dont check the value in column f so it will overwrite the color in column f even though there is a value in the cell. my version will only check the changed cell, but i can make it check every cell if you wish. — Plagon, Jun 22 '17 at 18:36
@UGP Unfortunately for some reason that didn't work. Perhaps I am doing a poor job of explaining what I need. When a certain code is entered in a cell in the D column, I would like the cell in the F column to turn red, the user clicks on the red cell, it takes them to a list of codes, they double-click a code, and it populates that cell in the F column and changes back to a no-fill color. Right now, when the code is entered in the D column, the cell in F column doesn't turn red which in turn doesn't make it clickable to the user. I'll update my question code again. Thank you for help so far! — anve, Jun 22 '17 at 19:25
Is the Code in the right Worksheet? Otherwise it makes no sense. The only reason why it does not make the Cell red is, if there already text in column f. — Plagon, Jun 22 '17 at 19:43
@UGP The code is in the main sheet/form worksheet. it is in the right worksheet and there is no other text in column F. It works as it should up until I add another code that turns the cell in column F red or any other data in entered in the same row. The cell doesn't stay red. Once more than one code is entered that turns the cell in the F column red, all of the other codes in the F column turn red, when really, they should stay as a no-fill background. So, if cell F7 has a code in it, and I enter a code in F10, F7 will go back to being red rather than staying as a no-fill background Thanks! — anve, Jun 26 '17 at 15:21
Ok now i understand. But why is there another code that can turn the cell red? when does it activate? It turns the other cell red or white again when you enter something below, because you took the version where it checks all cells. — Plagon, Jun 26 '17 at 15:49
@UGP There are many codes that when entered into the D column will turn the cell in the F column red to tell the user that cell in the F column needs a value. I need that cell in F column to turn red only when one of those listed values in my code are entered into the D column or when a code is deleted from the F column. Other than that, the cells in F column should remain as a no-fill background. Thanks! — anve, Jun 26 '17 at 16:09
I will try the second snip-it of code you posted looking at only the changing cell. — anve, Jun 26 '17 at 16:12
Now im confused. That should work, see the `Select Case` for the values in col `D` do change color. I thought you were talking about a VBA Code. So this does not make any sense to me. So wheres the problem? — Plagon, Jun 26 '17 at 16:18
@UGP I should've mentioned this before but, the values in column D are derived from a longer code in column C so, the values in column D are derived from a formula '=MID(cell in column C,16,6)". so the values in column D are taking the last 6 digits of a longer code in column C and if the last 6 digits equal to any of the codes listed out in the VBA code, that's when the cell in column F should turn red, really. I have adjusted my question to reflect that. Very sorry if not adding that part lead to the confusion. — anve, Jun 26 '17 at 16:21
Is the Value in Column C entered by the User? The `Worksheet_Change` only fires by manual input not formulas. So it could just check col `C` for the value. — Plagon, Jun 26 '17 at 16:32
@UGP On the main sheet/form, there is a button where the user clicks on and it will take them to a list of account codes, they will double-click on the account code and it will populate the cell in the C column. So, Column C's values are entered when a user double-clicks a value from a separate sheet of account codes. Then, column D reads the last 6 digits of the code in column C and if the last 4 digits of the code equal any of the codes listed out, it will turn the cell in column F red. I hope that helps. — anve, Jun 26 '17 at 17:18
There are 2 Options then. My Code activates on Col `D` or you dont use a formula for col `D` and when the user select the code, you also paste in the last 6 digits in col `D` (prefered). — Plagon, Jun 26 '17 at 17:52

Plagon · Answer 1 · 2017-06-22T18:46:32.990

Probably something like this:

Every Cell in Range:

    Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range: Set c = Union(Range("D7:D446"), Range("F7:F446"))
    Dim CellF As Range, CellD As Range, Cell As Range

If Not Application.Intersect(c, Range(Target.Address)) _
           Is Nothing Then

    For Each Cell In c
        Set CellF = Range("F" & Cell.Row)
        Set CellD = Range("D" & Cell.Row)

        If CellF.Value <> "" Then
            CellF.Interior.ColorIndex = 0
        Else
            Select Case CellD.Value
            Case "1000", "1000MN", "19FET", "20IDU", "20ONC", "20RT", "20DV", "20SPPR", "22DC", "22LF", "22ME", "530H", "60UBL", "74G1", "74GA", "74A9", "78RV"
                    CellF.Interior.ColorIndex = 3
            Case Else
                CellF.Interior.ColorIndex = 0
            End Select
        End If
    Next Cell
End If
End Sub

Only Changed Cell:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range: Set c = Union(Range("D7:D446"), Range("F7:F446"))
    Dim CellF As Range, CellD As Range, Cell As Range

If Not Application.Intersect(c, Range(Target.Address)) _
           Is Nothing Then

        Set CellF = Range("F" & Target.Row)
        Set CellD = Range("D" & Target.Row)

        If CellF.Value <> "" Then
            CellF.Interior.ColorIndex = 0
        Else
            Select Case CellD.Value
            Case "1000", "1000MN", "19FET", "20IDU", "20ONC", "20RT", "20DV", "20SPPR", "22DC", "22LF", "22ME", "530H", "60UBL", "74G1", "74GA", "74A9", "78RV"
                    CellF.Interior.ColorIndex = 3
            Case Else
                CellF.Interior.ColorIndex = 0
            End Select
        End If

End If
End Sub

score 0 · Answer 2 · answered Jun 22 '17 at 16:30

I think the easiest way to do it is just to add conditional formatting on Column F so that there's no fill if the cell is not blank. This link has more information on how to do it, but you basically do a NOT(ISBLANK()) within the conditional formatting box.

Changing cell color when value is in cell

2 Answers2