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
