For various reasons, I would prefer to use vba as opposed to
conditional formatting for my worksheet. If a duplicate value (in
this case, lengthy text entries) exists, change the font color to
blue. I can successfully write the macro for it but again, I would
prefer that it fire automatically on the Worksheet Selection Change
and I just can't figure how to make that work.

"=COUNTIF($D$1:$D$1000,D1)>1"

Any assistance greatly appreciated as always.

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range("$D$1:$D$1000"), Target) > 1
Then
Target.Interior.ColorIndex = 6
End If
End Sub

"(E-Mail Removed)" wrote:

> For various reasons, I would prefer to use vba as opposed to
> conditional formatting for my worksheet. If a duplicate value (in
> this case, lengthy text entries) exists, change the font color to
> blue. I can successfully write the macro for it but again, I would
> prefer that it fire automatically on the Worksheet Selection Change
> and I just can't figure how to make that work.
>
> "=COUNTIF($D$1:$D$1000,D1)>1"
>
> Any assistance greatly appreciated as always.
>

Try something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then ' In XL2007, use CountLarge, not Count
Exit Sub
End If
If Target.Text = vbNullString Then
Target.Font.ColorIndex = xlColorIndexAutomatic
Exit Sub
End If
If Application.WorksheetFunction.CountIf( _
Me.Range("A1:A100"), Target.Text) > 1 Then
Target.Font.ColorIndex = 3 ' red
Else
Target.Font.ColorIndex = xlColorIndexAutomatic
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




<(E-Mail Removed)> wrote in message
news:7effc494-ffd3-4a44-8907-(E-Mail Removed)...
> For various reasons, I would prefer to use vba as opposed to
> conditional formatting for my worksheet. If a duplicate value (in
> this case, lengthy text entries) exists, change the font color to
> blue. I can successfully write the macro for it but again, I would
> prefer that it fire automatically on the Worksheet Selection Change
> and I just can't figure how to make that work.
>
> "=COUNTIF($D$1:$D$1000,D1)>1"
>
> Any assistance greatly appreciated as always.

On Sep 6, 9:00*am, "Chip Pearson" <c...@cpearson.com> wrote:
> Try something like
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> * * If Target.Cells.Count > 1 Then ' In XL2007, use CountLarge, not Count
> * * * * Exit Sub
> * * End If
> * * If Target.Text = vbNullString Then
> * * * * Target.Font.ColorIndex = xlColorIndexAutomatic
> * * * * Exit Sub
> * * End If
> * * If Application.WorksheetFunction.CountIf( _
> * * * * * * * * Me.Range("A1:A100"), Target.Text) > 1 Then
> * * * * Target.Font.ColorIndex = 3 ' red
> * * Else
> * * * * Target.Font.ColorIndex = xlColorIndexAutomatic
> * * End If
> End Sub
>
> --
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> * *ExcelProduct Group
> Pearson Software Consulting, LLCwww.cpearson.com
> (email on web site)
>
> <MiataDia...@gmail.com> wrote in message
>
> news:7effc494-ffd3-4a44-8907-(E-Mail Removed)...
>
>
>
> > For various reasons, I would prefer to use vba as opposed to
> > conditional formatting for my worksheet. *If a duplicate value (in
> > this case, lengthy text entries) exists, change the font color to
> > blue. *I can successfully write the macro for it but again, I would
> > prefer that it fire automatically on the Worksheet Selection Change
> > and I just can't figure how to make that work.
>
> > "=COUNTIF($D$1:$D$1000,D1)>1"
>
> > Any assistance greatly appreciated as always.- Hide quoted text -
>
> - Show quoted text -

It seems so easy once someone gives you the answer. Hah! Thanks a
million to both of you