VBA Code To Find Dups, Highlight Them With Different Colors, Pretty Complicated

Aug 29, 2008

I'm trying to find some vba code that will find dups on my worksheet and highlight them in a special way: first dup = bright yellow, second dup = bright green, third dup = bright red, and if there is a forth, fifth or sixth dup, then just increment color number to next one, than next, etc...

I would like for the code to run as soon as there is a change on the worksheet (when I finish entering data in a row). I think I already have the right code to do that:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E9:E10000")) Is Nothing Then
Call dupSearch
End If
End Sub
I tested it out, seems to work as it should.

My major problem is that I need the code to check column F9:F10000 and compare the cells all along that column. That column concatenates 3 other cells in that same row (lastname&firstname&employee#), so if I run my dupSearch on that column only, I should find only real dups, and not just 2 people with the same name.

I also need to make sure it doesn't find empty cells, thinking they are dups. I would like to highlight column A to E of the row that contains the dup, and not just the cell in column F.

I have tried many other codes that I have found on the forum, but none of them work right with what I'm trying to do, and I just don't know enough VBA to make the changes I need.

View 9 Replies


ADVERTISEMENT

Code To Highlight Duplicates With Colors

Jun 12, 2014

I am using below code to highlight duplicates with different colors.

I want to change the range in B3:C2000

[Code] .....

View 3 Replies View Related

Find Columns By Name And Highlight Them Certain Colors Based On Text In Another Column

Jul 9, 2009

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................

View 12 Replies View Related

Using Different Colors To Highlight Duplicate Values?

Sep 17, 2013

I have rows that contain property identifiers and their owners. One property ID can have multiple owners. I would like to color the rows differently to show each unique property ID with their 1 or more property owners. I have attached a file showing what I would like (with fake data). In excel, I was able to figure out how to highlight the duplicate values, but it only does them in one color. I would like each property ID value to have it's own color - as I show in the attached file. In the file, I have value 1234 as one color, the 4546 values as another color and 2233 values as another.

View 3 Replies View Related

Highlight Duplicated With Alternating Colors

Feb 21, 2014

I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.

What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.

My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.

I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?

Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range

Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))

For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub

View 1 Replies View Related

VBA Code To Find Row Number Based On Data Via Input Box And Highlight Cell?

Jun 21, 2014

I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.

View 1 Replies View Related

Complicated Find Value Delete Row Macro?

Feb 15, 2014

In column B I have a list of blank cells and cells that contain values. I want a macro to search for 'Jobs' and if it finds this word in column B then delete the entire row where it is find and also delete the row below it if column B is blank and also delete the row below again if column B is blank (up to 8 rows below where the word is find at most will be blank)

When it eventually finds a cell that is not blank, then exit sub.

View 5 Replies View Related

Compare 2 Columns And Remove Dups

Feb 15, 2009

I have 2 columns of data (1 of 7000 and 1 of 39000). First column of 7000 entries should all be in the second column.

Example:
Record OLDRecord NEW
DBE2008/49IEP2007/0032
DBE2008/48IEP2007/0033
DBE2008/47DBE2008/47
DBE2008/46DBE2008/44
DBE2008/45RIE2008/0212
DBE2008/44TK2004/0073
DBE2008/43ID92/0522
DBE2008/42ID96/0274
IEP2007/0032DBE2008/41

I would like to compare these 2 columns and remove the 7000 entries from the second column. This can either be removed from the second column or resulted in another column. I am only quite new to Excel and formulas

View 5 Replies View Related

Colors The Zip Code That Matches The One Input

Aug 4, 2009

I am trying to build a simple spreadsheet for my salesman that given a customer's zip code, it looks up the corresponding territory code for that zip code. I have conditional formatting setup so that it colors the zip code that matches the one inputted, but how do I then return the value of the corresponding territory code. The spreadsheet being searched is a simple 2 column list, with the headings: Zip Code and Territory. What's the best way to get the territory code back to my worksheet?

View 9 Replies View Related

Excel Pallet Lost Colors (hovering Displays Colors But Visual Clues Are Not Shown)

Jul 7, 2013

My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.

I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.

View 12 Replies View Related

Creating A Validation List, Referencing Other Lists, Remove Dups

Jun 25, 2006

I tried using several of the formulas and VBA codes from other posts, but they just didn't work out quite right. And since I'm worthless at VBA coding, my changes either did nothing, ruined the code entirely, or blew up my computer : D . The attached doc should explain everything.

View 9 Replies View Related

Find The Lack Of Contrasting Colors In The "theme" Portion Of The Color Palette

Feb 3, 2009

I find the lack of contrasting colors in the "theme" portion of the color palette useless. For instance--- I TYPICALLY do not need 5 shades of similar blue that I cannot visually differentiate within the color pallette of my working area.

I also need colors that more visually contrast one another and pop out (closer to the row of standard colors that is given or those of prior versions of excel).

Is there a way to add a custom row of colors with a custom title in the color pallette like the "theme" colors have or the "Standard" or the "Recent Colors" ?

If that can't be done...is anyone privy to the syntax for changing the Recent Colors area?

I'd like to add for instance the below three colors to the default recent colors of each new workbook...

Selection.Interior.ColorIndex = 4 '(Bright Green)
Selection.Interior.ColorIndex = 7 '(Bright Magenta)
Selection.Interior.ColorIndex = 37 '(Bright Blue)

View 9 Replies View Related

Find Value In Range Then Highlight Row?

Apr 7, 2014

I am trying to find a value in a range, then highlight the row that has that value. this is what i have found so far, i just cant get it to work:

Code: [URL] .......

View 14 Replies View Related

Find And Highlight Text ...

Oct 10, 2007

Each cell in Range AL3..AL50000 have text (sentence - description of problem).

Cell AL1 has my search word (i.e. loose)

I would like to search range AL3..AL5000 for word "Loose" - if found highlight every cell where was found - Yellow.

If AL1 is empty - remove all highlights.

Here is what I have so far (patches of codes found in this forum)

Sub FindTextInCell()

Dim ws As Worksheet
Dim myText As String

myText = Sheets("detail_report").Range("AL1").Value

If myText = "" Then Exit Sub

Set Found = Range("AK2..AK56000").Find(What:=myText, LookIn:=xlValues, lookAt:=xlPart, MatchCase:=False)

If Not Found Is Nothing Then

Found.Interior.ColorIndex = 36

End If
End Sub

My code highlights only first found cell and if I type any other search word - highlight from the 1st search does not get removed.

View 9 Replies View Related

Insert Clear Highlight Code

Dec 30, 2013

I got a code in internet that highlight a cell with equal value after double clicking a cell.

But my problem is I want to clear highlighted cell after clicking another cell (making double clicked cell inactive).

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Dim wksh As Worksheet
Static ColIdx
Cancel = True

[Code] ..........

View 3 Replies View Related

VBA Code To Highlight Every Other Duplicate / Single

Sep 2, 2003

I would like to:
1 - Highlight the row for the first duplicate/single "Ana M."
2 - Then skip the next duplicate/single row "Jane S."
3 - Then Highlight the row for next duplicate/single "Sam"
4 - .... and so on.

Sample Data:
NameStreetPhone
Ana M.12 A St333-3333
Ana M.23 Z St333-3333
Jane S.12 A St555-5555
Jane S.15 Z St555-5555
Sam A.55 A St222-2222
Tony J.45 A St444-4444
Tony J.11 B St444-4444
Tony J.66 Z St444-4444

View 8 Replies View Related

Find Like Numbers Over Two Spreadsheets And Highlight

Mar 16, 2014

I have two spreadsheets. Spreadsheet 1 has 6 columns and column E has a list of numbers. It runs from E2:E739.

Spreadsheet 2 has 9 columns and column A has the numbers I need matched against. Column A runs from A9:A5027. How can I find the numbers from Spreadsheet 1, Column E that are also in Spreadsheet 2, Column A. Once matched I would like to highlight the ROW on spreadsheet 1 that has a match.

View 3 Replies View Related

Find Number And Highlight In Yellow

Jan 18, 2014

I would like to have a macro that searches a selected area for a number (there may be many of them in the spreadsheet) and highlight it in yellow. Please be noted that I don't only want it to search and highlight the number in its respective order, but also every one of its possible arrangement. To make it easier, please also be noted that all of the numbers I will need to search are only 3 digit numbers.

Refer to the attached document : Highlight Number Desired Results.xlsx‎

View 14 Replies View Related

Highlight Selected Cell When Using FIND

Jun 12, 2008

I have a worksheet with lots of data on it, resulting in a very busy layout. I have been using "FIND" to search for various numbers within the spreadsheet, however often times it is hard to see which cell "FIND" selects.

Is there a way to enable Excel to highlight the selected cell? For example could "Find" turn the cell yellow when it finds a match?

View 14 Replies View Related

Find And Highlight Specific Numbers

Jan 22, 2009

I have a spreadsheet with a stock list. Each item has an individual number. I have a code below, which I have doctored from other various sources. The problem I have is that if I search for number '12' the spreadsheet highlights every cell with 12 in (120 121 122 123 etc...) Is there any way I can change the code so that it returns only the 12 and not every cell that contains 12? The current code is as follows:

View 2 Replies View Related

Find, Highlight Across And Sumif Macro

Jun 9, 2009

I have a large spread sheet. I need to find 38 accounts in the spreadsheet, highlight them across the span of the spread sheet and then do a sum if to the side. I need to use this Macro monthly on a new ageing and some months some of the accounts may not be in the ageing.

View 14 Replies View Related

Find & Highlight Duplicate Data

Aug 2, 2009

see attached sheet. Column A has File Name. Need to highlight the duplicate data as you can see there is 2 duplicate data which i have manually highted ( C19 is duplicate of C12, C83 is duplicate of C84).

View 4 Replies View Related

Find A Word In Entirebook And Highlight It.

Nov 26, 2009

I need a macro to find a word in entire workbook and need to highlight it.

View 13 Replies View Related

Find Min 2 And Max 2 Cell Values Then Highlight

Jan 22, 2009

I want to identify the 2 minimum valued greater than 0.00% in rows with percentages only. Then identify the 2 maximum values greater than 0.00% in rows with percentages only. The sheet changes in size (rows and columns) each day. C4 Row 4 is a constant starting place if that is necessary. I have been tweaking this code all night, but no luck. I keep getting a highlighted yellow error in editor @ oRg.Find(What:=iMin, _
I also don't know what an oRg is, I am using 2001, that may be the problem.

View 10 Replies View Related

Find & Highlight Words In Cell

Mar 25, 2007

I have received a workbook containing lists, unfortunately the names are listed in individual cells - several per cell, separated by commas. I need to highlight wherever a particular name occurs without splitting the data from the cell, ie like Find but within the cell.

View 9 Replies View Related

VBA Code For Highlight Today And Yesterday Entry

Feb 3, 2012

I have a sheet where i enter values daily i want those entry who enter today and yesterday Highlighted.

Today=Yellow
Yesterday=green

View 4 Replies View Related

Code To Look At Sheet2 And Highlight Any Difference On Sheet1

Feb 15, 2013

I need a code that will look at each row on sheet 2 then highlight any cell that is different to the corresponding row/cell on sheet 1.

View 4 Replies View Related

Quick Way To Find And Highlight Multiple Rows?

Feb 14, 2014

I have a very large spreadsheet (46,000 rows). There are a couple hundred rows I need to find and highlight, and also insert potentially-linked contract numbers in a second column.

For example:

Column 1: Column 2:
Contract # Linked Contract #
12345 67890

Is there a quick way to do this without using find and replace to highlight all several hundred of these rows? I have an hour and a half until I leave work and my boss wants it today! Compounded is the problem that there are about 100 versions of this spreadsheet I need to do this for.

View 12 Replies View Related

Macro To Find Unique Values And Highlight Them?

Apr 9, 2009

Is there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.

They should find uniques using all 3 columns, not find them within each column.

I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).

View 9 Replies View Related

Find And Highlight Most Frequently Occurring Number

Feb 5, 2012

I have 12,000 rows on my excel spread sheet of data and my average volume for all those days are 17,661,398.6
I noticed that the so called average did not show up not even once in all of the 12,000 rows. Thus I conclude that the average is not normal ? How to find & highlight the most frequently occurring number, the usual, and or the normal numbers?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved