Macro To Compare Rows Of Data By Colour
Jun 5, 2009
I have the below macro (kindly provide by VoG) which compares rows of data from columns H-P, excluding M, where two rows match across the criteria, the macro takes the first of these matches and copies to sheet2, where there is no match it copies each entry to sheet 3. It works fine except for 1 thing, the data that I am comparing comes in 2 types of rows, coloured yellow and green (color codes 36, 35 in Excel 2003). I would like to add a further condition so that the macro will not match off where 2 yellow or green rows appear together and where a yellow and green row match it will take copy the yellow row to sheet 2 regardless of whether the yellow or green appear first in sequence.
Sub Cmpare()
Dim i As Long, LR As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add 'matches
Set ws3 = Sheets.Add 'non-matches
j = 1
k = 1
With ws1
LR = .Range("H" & Rows.Count).End(xlUp).Row
For i = 2 To LR Step 2
Retry:
If .Range("H" & i).Value = .Range("H" & i + 1).Value And _
Abs(.Range("I" & i).Value - .Range("I" & i + 1).Value)
View 9 Replies
ADVERTISEMENT
Jan 22, 2009
I've got spreadsheet with lots of data in and the most important data is located in column C which determines the colour of that whole row. The numbers in that column are 9 digits (i.e. 180041960). If that number is the same in the next row highlight both rows of filled cells in yellow and continue to highlight in same colour until the number changes. As the next number is different leave colour as it is until the number changes again then go back to yellow and change colour of rows. Continue the procces until end of spreadsheet.
View 9 Replies
View Related
Aug 13, 2012
I have a big database, each row is having it's colour. How can I sort by colour? I mean, data with one colour comes first, then data with another colour comes after etc.
View 1 Replies
View Related
Oct 3, 2012
I would like it to loop through all the charts on the "Graphs" sheet.
It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.
VB:
Sub Colour_Chart_working()
Dim rngColors As Range
Dim rngCell As Range
[Code].....
View 1 Replies
View Related
Sep 26, 2012
I need to compare 2 worksheets, one containing a list of IP adresses that need to be checked (interfaces), and another containing a list of IP addresses that have been checked (Scanned hosts), there are over 100000 scanned host entries.
I need to search the "Scanned hosts" worksheet to ensure that all of the IP addresses listed in the "Interfaces" worksheet have been scanned. I then need to place an entry next to the IP address on the "interfaces" worksheet to indicate the check date and the name of the person who checked it. It also needs to change the colour of the cells to indicate if it has been found or not found.
The code I have written so far (from piecing together different threads from MrExcel), at first glance, seems to work perfectly. However, if I replace an ip address in the "interfaces" worksheet, with one that I know does not exist - it still marks it as found.
Interfaces;
IP Address
System
Date Checked
Checked By
1.2.3.4
fred bloggs
1.2.3.5
fred bloggs
[Code] .....
Scanned hosts is just a list of IP addresses and text (168000 rows) in a single column. Format is xx.xx.xx.xx ^IP^^^^Jul 29 2012 08:01:29:000PM
Sub Compare()
Dim X As Long, CurrentRow As Long, FindRow As Long, WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Interfaces")
Set WS2 = Worksheets("Scanned Hosts")
CurrentRow = 1
[Code] .....
View 4 Replies
View Related
Apr 20, 2012
I have two worksheets of information (hopefully the same information but from two very different sources). There is a unique identifier to each row (i.e a membership number), in col A
I want to start with the membership number in cell A1 on sheet 1 and look for that membership number on sheet 2 (also col A). If there is a match, it copies the matching row from sheet 2 to sheet 3 (pasting it in a new row starting at 1)
If it does not match then the cell in sheet 1 is highlighted
View 2 Replies
View Related
Jun 28, 2007
I need to run the same database dump every day and find the differences. Both spreadsheets will have the same column headings, with Column A being the lookup value. Each spreadsheet will have many columns of data. I need to find the differences from Row 1 on spreadsheet A with Row 1 on spreadsheet B.
Example:
Spreadsheet 1, Column A value is 900026. Need to find 900026 in Column A on Spreadsheet 2. Compare all the data in each column to see what has changed. If anything has changed on Spreadsheet, copy the row from Spreadsheet 2 onto a new sheet and 'tag' which data has changed.
Also, again using Column A as the identifier, if there are rows on data on Spreadsheet 1 that do not appear on Spreadsheet 2, copy the entire row onto a new sheet and tag it as "Not on new datadump". And vice versa, is there are rows of data on Spreadsheet 2 that do not appear on Spreadsheet 1, copy the rows of data onta a new sheet and tag it as "Not on old datadump".
View 9 Replies
View Related
Feb 4, 2007
VB code to carry out the following task?
Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.
If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40
View 9 Replies
View Related
Jun 24, 2014
I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.
For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.
View 3 Replies
View Related
Apr 12, 2012
I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.
I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.
So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.
View 2 Replies
View Related
May 9, 2013
I have account numbers in Col A and in Col D .I would like a macro to highlight the color the items in Col D that are not in col D. Blanks can be ignored
View 3 Replies
View Related
Nov 24, 2011
I'm creating a worksheet which tracks pupils progress in our school. down the left there is a column to put in the level they came into our school on - eg 1C or 3B etc.. to the right of the level is a colour coded row which shows Pink, Yellow and Green - if the pupil makes it into the green part of their level by the time they leave then they are on target.
Basically I have a column of cells which use a data validation list which is linked to another sheet with a list of levels. What I want to happen is when I select the level - it fills the row to the right with the correct colours.. I've attached a picture to show you what i'm trying to do.
Reason being is - we might have 6 kids on L3, and 1 on L1 so the number of rows needed for each level changes - at the moment I'm having to copy and paste - which although is easy enough, it is a bit tedious when you have a whole bunch of pupils to put on the grid.
I can't find the option in imageshack to use a thumbnail
View 6 Replies
View Related
Nov 30, 2009
need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
View 5 Replies
View Related
Apr 25, 2014
Creating a Macro which compare the Customer ID's present in Column H of Sample1 file with Column B of both Sample1 and Sample2 files.
The Count of Sample1 file should come in Column I and the count of Sample2 file should come in Column J of Sample1 file.
After above steps macro should automatically pick the lowest value (value should be greater than 0, if value is 0 than macro should consider it as blank) from Column I and J and paste it to Column K.
In Sample1 file I had also shown that how the data should look after running the Macro.
View 2 Replies
View Related
May 8, 2006
I am trying to use a macro to compare data from 2 sheets and spit out matching data in a third sheet. The first sheet has two columns of data. the second sheet has 4 columns of data. Only one column from sheet 1 and 1 column from sheet 2 will actually have matching data.
All other columns in sheet 1 and 2 are associated to the matching columns and must "tag along" into the 3rd sheet. I would love the third sheet to have a button that I just click and it populates the data.
The macro will be comparing between 50,000 and 500,000 items. Possibly a good book to use or even a referral for a proffesioanl or even some good heartedness,
View 5 Replies
View Related
Oct 12, 2011
I have a worksheet with 40 colums and 9200 rows. There are columns with empty data in the rows. Can I have a macro to copy data in the rows in Column E, F, I, J K and L to empty rows below?
View 3 Replies
View Related
Aug 13, 2009
I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).
View 10 Replies
View Related
Feb 12, 2008
My Objective: Simplified
Assuming a range A1:G15
A Validation list in column H
The Validation List options are: Blue,Pink,Green,Black,Grey,Yellow,Orange and None or '
how I create a Case Select code to change, the row range dependant on the option selected in the validation list.....or most efficient code to achieve my objective.
Or the most efficient code method to achieve the outcome I am seeking.
What I can't figure out 1:
How to incorporate the validation list selection to recognise the colour option selected.
What I can't figure out 2:
I want to be able to format a row any of colours in the list, so if it is black, would I need to clear the black format before changing it to say Orange, or is it possible to go straight from black to Orange?
What I have tried:
I am aware of the limitation of three formats using "Conditional Formatting" so want to expand the colour formatting possbilities using code, by selecting choice of format colour from a validation list.
I did a macro recording, selecting the appropriate option within the Validation List, and then formatting a row range....I experimented with the resulting code to no success, although I have a copy of VBA and Macros for MS Excel....theres a lot to think about....and I can't quite grasp what's required.
Code I have used in the passed for a similar but different worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
View 9 Replies
View Related
Mar 30, 2009
I have 11 names, one name in each cell, in one row. I want to be able to delete a row if there are more than 3 names in that row that have the same font colour, eg red. Would have to be a loop type macro to check all of the rows on the one worksheet.
View 9 Replies
View Related
Dec 27, 2006
I have some code that consolidates data from 4 sheets in different workbooks into one sheet on a separate workbook. I'd like to add a validation check to make sure that all rows are copied and pasted.
Specifically, I would like to total the # of rows in each worksheet and compare that # to the total # of rows in the consolidated worksheet. On some of the worksheets, the code deletes the header row and/or the last row. So, the count of the rows should happen after those rows are deleted. If not all the rows are copied, a message box should pop up saying "Not all rows copied".
The full code is posted in the linked post below which includes the workbook names, sheet names, etc.:
http://www.mrexcel.com/board2/viewto...112&highlight=
View 9 Replies
View Related
Feb 28, 2009
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
View 9 Replies
View Related
Oct 3, 2012
As far as I can tell Conditional Formatting will not work for this problem.
What I need now is some code to colour the rows in the data sheet ("Standards_Data"), so some other code (which is working fine) can then colour data points on a multiple graphs (which will be on the "Graphs" sheet). I've played around with as much code as I can find but nothing has the flexibility that I need or doesn't seem to work at all.
The idea is that users can adjust the colours using the Column A on the "Graphs" sheet to best highlight certain things. Also this list could then be edited as old Standard ID's become redundant and new ones come on line.
So in a nutshell: Colour rows in "Standard_Data" based on value of Standard ID (Column 4) according to the corresponding colour found in the list in the "Graphs" sheet.
I've attached a trimmed down version of the workbook.
View 4 Replies
View Related
Feb 13, 2014
Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?
View 3 Replies
View Related
Apr 17, 2014
I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.
I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.
View 2 Replies
View Related
Apr 30, 2014
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
View 1 Replies
View Related
Apr 15, 2014
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies
View Related
Feb 23, 2010
I am looking for a vba which will send an email to someone the minute I highlight a row in red?
View 9 Replies
View Related
May 23, 2014
A column of cells can be 1o different shades of green according to their value (achieved by using conditional formatting.) I want to arrange that at a certain time all the cells of a part of the row of a formatted cell are the same colour.
View 4 Replies
View Related
Jan 29, 2010
The data is spread across worksheets however the first column of every worksheet contains same data (eg. Name). Evenso, the rows wont correspond each other across worksheets. eg.
WS1
Name: Variable 1: Variable 2
John : X : Y
Mary : A : F
Mary : F : W
Lance : E : G
Lance : R : T
Lance : D : W
Neil : R : H
Neil : H : S
WS2
Name: Variable 1: Variable 2
John : X : Y
Mary : A : F
Lance : E : G
Lance : D : W
Neil : R : H
Neil : H : S
Neil : G : W
Is there anyway to correspond these rows and add blank rows so that if Lance is in 3 rows in WS 1 it will be the same in WS 2 as well even though some rows are blank? And vice versa?
View 11 Replies
View Related
Aug 22, 2006
I have 2 sheets that have 15 columns and approximately 200 rows on average, but the number of rows varies from week to week. I need to compare the information on sheet 3 to the information on sheet 1 by row. There are actually 3 worksheets total, but the 2nd worksheet is not involded in this macro. If the 2nd-14th columns are an exact match, I need to have the date from the first column and the notes from the 15th column (the formatting has to be copied too!!!) on the first sheet copied over to the matching row on the third sheet into the 1st and 15th columns, and the 16th-18th rows on the first sheet need to be copied over to the third sheet as well. After all this is done, the row on the first sheet needs to be deleted.
I need it to compare all of the rows this way, and unfortunately, I think it is going to have to loop through every row on the 1st sheet for every row it is comparing from the 3rd sheet.
I know this will take quite a while to compare, but given this takes me a couple hours on average, letting the macro run for 5 or 10 minutes is a vast improvement
View 9 Replies
View Related