Hi Chris, You could do this with VBA, but it's better, and easier, done with conditional formatting. If you are using XL2003 or earlier, select Col B, then put this formula into conditional formating: =COUNTIF(A:A,A1)>1 Set your format as desired. If you've not used conditional format before, read the Help, or write back to the group. If you're using XL2007, it's better not to use whole column refs, so you would need to specify a range something greater than your expected data. For example: =COUNTIF($A$1:$A$2000,A1)>1 I've attached a workbook for you to look at. Regards - Dave
> Date: Fri, 11 Dec 2009 05:40:22 -0800
> Subject: $$Excel-Macros$$ Alert for repeating cell information
> From: gapp...@hotmail.com
> To: excel-macros@googlegroups.com
>
> 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
_________________________________________________________________
Use Messenger in your Hotmail inbox Find out how
http://windowslive.ninemsn.com.au/hotmail/article/823454/web-im-for-hotmail-is-here
--
----------------------------------------------------------------------------------
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
Chris.xls
Description: MS-Excel spreadsheet
