Re: $$Excel-Macros$$ Alert for repeating cell information
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 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
> To: MS EXCEL AND VBA MACROS
> 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 man
Re: $$Excel-Macros$$ Alert for repeating cell information
Many thanks for the responses! The two that have been provided work
perfectly. Thanks also for the workbook, it's a brilliant starting
point. I think I'll step into the shallow end from now on, not the
deep end :)
On Dec 12, 1:13 am, Dave Bonallack wrote:
> 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
> > 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
>
> _
> Use Messenger in your Hotmail inbox Find out
> howhttp://windowslive.ninemsn.com.au/hotmail/article/823454/web-im-for-h...
>
> Chris.xls
> 19KViewDownload
--
--
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
Re: $$Excel-Macros$$ Alert for repeating cell information
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
To: MS EXCEL AND VBA MACROS
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
RE: $$Excel-Macros$$ Alert for repeating cell information
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
Re: $$Excel-Macros$$ Alert for repeating cell information
Agree with Dave, conditional formatting is fast and better way as far as
the conditions and resultant color are limited per consideration.
Best Regards,
--
DILIP KUMAR PANDEY
MBA-HR,B COM(Hons.),BCA
Mobile: +91 9810929744
dilipan...@gmail.com
dilipan...@yahoo.com
New Delhi - 110062
On 12/12/09, Dave Bonallack wrote:
>
> 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
>
> --
> Find out how Use Messenger in your Hotmail
> inbox
>
> --
>
> --
> 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.blogs
