Many thanks for the responses! The two that have been provided work
perfectly. I think I'll step into the shallow end from now on, not the
deep end :)
On Dec 11, 7:20 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> 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
> athttp://www.excelitems.com
> 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> 4. Excel Tips and Tricks athttp://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 athttp://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
