How is it that you WANT it to function?
do you want it to check when you ENTER a value?
or do you just want a macro that you can execute
that will change the color?
this would work for the second scenario:
Option Explicit
Sub Flag_Dup()
Dim Dict_CellVal, I, RowCnt
'Create Dictionary Object to count unique values
Set Dict_CellVal = CreateObject("Scripting.Dictionary")
Dict_CellVal.RemoveAll
'---------------------------------------
Columns("A:A").Interior.ColorIndex = 0 'clear colors
'---------------------------------------
' count number of rows that have content
RowCnt = Application.WorksheetFunction.CountA(Columns("A:A"))
'---------------------------------------
' Loop through rows
'---------------------------------------
For I = 1 To RowCnt
If (Not Dict_CellVal.exists(Cells(I, "A").Value)) Then
'Add unique value to Dictionary Object
Dict_CellVal.Add Cells(I, "A").Value, 1
Else
'Increment count if duplicate is encountered
Dict_CellVal.Item(Cells(I, "A").Value) = Dict_CellVal.Item(Cells(I,
"A").Value) + 1
End If
Next I
'---------------------------------------
' highlight rows where count > 1
'---------------------------------------
For I = 1 To RowCnt
If Dict_CellVal.Item(Cells(I, "A").Value) > 1 Then
Cells(I, "A").Interior.ColorIndex = 3
End If
Next I
End Sub
(did 10,000 rows in less than 1 second!)
Paul
________________________________
From: ChrisInHolland <gapp...@hotmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, December 11, 2009 8:40:22 AM
Subject: $$Excel-Macros$$ Alert for repeating cell information
Hi,
I'm trying to write some code that will create an alert when a
particular string value is found more than once in a specified range.
For exmple, in Col A i have:
cat
cat
cat
mouse
dog
dog
I need a script that will count how many times I have "cat" written
and if that value is >1 I want it to change the colour of the adjacent
cell in Col B. I then want it to repeat this for "mouse", "dog" and
many 100's of other string values in Col A. In the above case the
cells B1, B2, B3, B5 & B6 would change colour but not B4.
Any help on how I'd write this would be greatly appreciated!
Being my first foray in VBA scripting I'm hopelessly outgunned when it
comes to writing code for the above. What I've come up with so far is
the following which works for finding only 1 string at a time (the one
that I specify, A1 in below example. Specifying each string I want to
search is not practical given many hundreds of search strings that
will be present in Col A).
Dim R As Integer
Dim x As Integer
Dim Cell As Range
For Each Cell In Range("A1:A100")
If InStr(1, Cell, [A1]) > 0 Then
R = 1
x = x + R
End If
Next Cell
Range("E1") = x
If x >= 2 Then
ActiveCell.Interior.ColorIndex = 3
End If
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
