Help needed with getting text to turn red on new input via VBA

Cirnex

New Member
Joined
Jun 15, 2015
Messages
1
I found this nice code that keeps a drop down's info and adds to it with a "," then the next input. When you select the same drop down item it gets rid of that item from the list in the cell. However i need to add a third option.
I need it to add a new item on the first item choice. The second choice of the same item needs to turn the text for that one item red in the list. (Eg: chair, lamp, dog. Only Lamp would be red if it was chosen a second time.) and then on the third choice it gets rid of the choice in the list as the code does currently. I was trying change the dim of newval to a Font.Color = RGB (255, 0 , 0) but i can't seem to get the Dim to work. Any suggestions.
Code below:

PHP:
Option Explicit
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim lUsed As Long Dim clrRed As Long
  If Target.Count > 1 Then GoTo exitHandler
  If Target.Column = 1 Then
  On Error Resume Next   
Set rngDV = Target.EntireColumn.SpecialCells(xlCellTypeAllValidation)   
On Error GoTo exitHandler
  If rngDV Is Nothing Then GoTo exitHandler
  If Intersect(Target, rngDV) Is Nothing Then     'do nothing   Else

   Application.EnableEvents = False
 newVal = Target.Value
 Application.Undo
 oldVal = Target.Value
 Target.Value = newVal

 If oldVal = "" Then
    'do nothing
 Else
    If newVal = "" Then
       'do nothing
    Else
       lUsed = InStr(1, oldVal, newVal)
       If lUsed > 0 Then
          If Right(oldVal, Len(newVal)) = newVal Then
            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
          Else
            Target.Value = Replace(oldVal, newVal & ", ", "")
          End If
       Else
          Target.Value = oldVal _
             & ", " & newVal
       End If

     End If
 End If
  End If 
End If 
exitHandler:   
Application.EnableEvents = True 
End Sub

Any help or guidance would be greatly appreciated.
Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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