Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Font Color for Dupes on Selection Change
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Font Color for Dupes on Selection Change
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 " 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Font Color for Dupes on Selection Change
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) 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox to change background color, font color and remove/ add bo | Excel Discussion (Misc queries) | |||
change font color | New Users to Excel | |||
how can I conditionally change font color, or background color? | Excel Worksheet Functions | |||
How to change the default Border, Font Color, and Cell Color | Excel Discussion (Misc queries) | |||
Why doesn't the font color change? | Excel Programming |