Microsoft Office Forums - View Single Post - [Solved] Change color according with dropdown selection

Guessed · 08-25-2016, 05:02 AM

Setting the background colour relies heavily on the pattern you have applied to the cell. It would be simpler to set the foreground colour instead. Since the code chooses the cell based on the location of the CC, you can treat both CCs the same way

Code:

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  Dim StrPwd As String, iCol As Long
  StrPwd = "DocumentPassword"
  
  With aCC
    Select Case .Title
      Case "Lst1", "Lst2"
        'MsgBox .Range.Text
        Select Case .Range.Text
          Case "High"
            iCol = wdRed
          Case "Medium"
            iCol = wdYellow
          Case "Low"
            iCol = wdBrightGreen
          Case Else
            iCol = wdWhite
        End Select
        If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
        .Range.Cells(1).Shading.ForegroundPatternColorIndex = iCol
        ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
    End Select
  End With
End Sub