Change Font Color for Dupes on Selection Change
On Sep 6, 9:00*am, "Chip Pearson" 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)
wrote in message
...
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
|