Compare Two Worksheets (including Formulas) And Highlight Different Cells

Oct 17, 2012

I want to design a macro that will compare the student's completed worksheet to the model answer worksheet. This will allow the class to work at different paces depending on ability.

For each exercise, the two sheets should look identical; and so, I would like the macro to highlight any different cells in the student sheet in a colour. I would also like cells with identical values, but different formulas to be highlighted - this is why I could not use the solution posted in : "Compare two worksheets and highlight the one sheet's difference from the other one".

View 1 Replies


ADVERTISEMENT

Compare Worksheets & Highlight 1 By Criteria

Feb 18, 2008

I have read other threads dealing with this issue, but none seem to help me! I have a workbook with two worksheets, both with the same number of rows in them. I want each cell in worksheet1 to be compared to the cell in the same row and column in worksheet2, and if the worksheet1 cell is greater in value then the cell should turn red. I have tried the previously mentioned tactic of selecting the whole column in worksheet2, assigning it a name, selecting the column in worksheet1, and applying conditional formating based on the name. However, the result is always that the whole column in worksheet1 turns red, regardless of whether the cells are greater in value or not.

View 9 Replies View Related

How To Compare Multiple Values Including TIME And Compare Rate

Feb 21, 2014

Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.

SampleRateExamine.xlsx‎

View 3 Replies View Related

Compare Values In Two Cells - Highlight When There Is No Match

Jan 3, 2013

I have two cells with values that are strings.

I would like to know of a macro that can compare each two cells(start from E2 and compare with E3)to see if the values are NOT equal.

If they are equal, do nothing and if they are NOT, highlight the value.

Example:
compare E2&E3, E4,E5,E6,E7,.... if for example E2&E3, E4,E5,E6,E7,.... each hat same string, do nothing, but if they were not the same highlight it with red.

This code checks two cells and highlights one cell when there is duplicate, I like the opposite one, but don't know what to change!

Code:

Sub CompareCells() Dim r As Range, cell As Range Range
("E2", Range("E" & Rows.Count).End(xlUp)).Name = ("MyRange") Range("MyRange").Select
' clear all colors from selection Selection.Interior.ColorIndex = xlNone
' loop through cells and compare Set r = Selection Set r = Selection.Resize(r.Rows.Count + 1)
For Each cell In r If cell.Offset(1, 0).Value cell.Value Then
cell.Offset(1, 0).Interior.ColorIndex = 3 End If Next End Sub

View 3 Replies View Related

Compare Two Sheets - Cells Highlight If Difference Is More Or Less Than 5%?

Mar 19, 2014

I have the following code, which works great, but I need the cells to highlight only when the difference is more or less than 5%

how can I adjust for this?

Code:
Sub RunCompare()Dim sheet1 As String
Dim sheet2 As String
sheet1 = InputBox("What is the First Sheet Name?")
sheet2 = InputBox("What is the Second Sheet Name?")
Call compareSheets(sheet1, sheet2)

[code].....

View 3 Replies View Related

Compare Cells In Different Workbooks & Highlight Differences

Dec 6, 2007

I am trying to find a way to compare the cells (example: D4:D12 to the same cell range in another workbook. If the value in D4 is less than D4 in workbook 2 highlight it red and if it is greater highlight it green. I also want a loop to go through the specified range. Of course, the scale of the worksheets is much greater.
Another issue I foresee, is that the workbook name that I am comparing to changes every week, so is there a way to handle this change easily?

View 2 Replies View Related

Compare Formulas In Cells

Dec 20, 2006

i wonder if there is a way to compare the formulas in two cells and not their result. I use a worksheet as a model and i want to track unwanted changes in the formulas between any new worksheets created and my original worksheet.

View 9 Replies View Related

Conditional Formatting To Highlight Cells With Formulas

Dec 29, 2008

I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?

View 9 Replies View Related

Conditional Formatting To Highlight Cells With Formulas (versus Constants)

Nov 24, 2010

Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?

I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.

If I use functions like FIND, they look at the result of a formula, and not the formula itself.

I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.

View 7 Replies View Related

Conditional Format - Highlight Cells In Column That Have Entered Numbers Rather Than Formulas

May 29, 2013

Col A - budget items (description)
Col B - budget dollars
Col C - actual dollars
Row 10 - summation Cols B and C

When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.

How can I highlight the cells in Col C that have entered numbers rather than formulas.

View 1 Replies View Related

Compare Specific Cells From Three Separate Worksheets

Mar 27, 2009

Is there a way to compare specific data from three separate worksheets to see if they are equal?

I receive three daily reports that contain some of the same information that must match in order to correctly compile an Executive Report. Currently, I am doing this manually. Is there any way to automate the process to have a warning or something similar pop up if the numbers do not match?

View 2 Replies View Related

VBA To Copy / Paste (including Formulas) To Next Available Row

Mar 20, 2014

I need some VBA to copy row 2 then paste it (including formulas) into the next available row in the same sheet but then also clear the data in row 2 but retain the formulas.

View 2 Replies View Related

Compare Worksheets - Change Colour Of Cells To Indicate If Found Or Not

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

Compare Values From Different Worksheets And Write In Adjacent Cells

Aug 17, 2013

I am new to programming in excel but am looking to make a VBA that allows me to see if a value entered exists in a second spread sheet. If it does then write a comment next to it. If not then write a different comment.

This is what my spread sheet currently looks like:

Sheet1 contains all the important information:
A
B
C
D
E
F

Load
PO
Delivery
Email
Item
ShipCon

1
3456
6543
Y
5219

5129

2
2345
5432
Y
1234

4321

Sheet2 contains the items that contain a shipping condition. There are two shipping conditions Fragile and non-Fragile. The list is of Fragile items.

Item

5219

1234

I am looking for a way to have it set up that when i enter an item number in sheet in 1 it searches in sheet 2 to see if it is fragile, if it is then returns, in sheet 1 column F next to that specific item 'Fragile'. If it doesn't find it in the list returns 'non-fragile'.

View 4 Replies View Related

Check For Values In Range & Compare 2 Cells On Different Worksheets

Aug 22, 2008

In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;

Where 0.05 selected it computes for cell C63:

=IF( COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!L55,"YES","NO")

then cell D63

=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!AA55,"YES","NO")

and so on

Then if 0.01 selected it would compute for Cell C63

=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!K55,"YES","NO")

then cell D63

=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!Z55,"YES","NO")

View 6 Replies View Related

Saving To Seperate Workbooks Without Including Formulas

Nov 1, 2007

I have a code (which I have posted below) which I slightly modified and was kindly provided for me by 'antoka05'

It saves all my worksheets to seperate files which works great. :D

What I wanted to know is can I modify this code so that when it saves the worksheets to seperate workbooks that it just saves the values and not the formulas that are in the cells.

Also I need to remove the password from the sheets before they are saved, could this also be incorporated into the code?

View 11 Replies View Related

Compare Actual Grade To Potential Grade And Highlight Cells

Jul 10, 2013

I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.

I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.

One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.

View 7 Replies View Related

Compare, Add, And Highlight

Jun 23, 2008

How would you add rows with the same name and then if they are equal in added amount, to highlight them in red?

example:

a b c

xyz x 50 (highlight in red)
zzyy x 50
zzyy z 50
zzyy x 50
xyz z 50 (highlight in red)

zzyy is x = 100 and z = 50 so it would not be highlighted in red.

but xyz is x =50 and z =50 so it is highlighted.

View 9 Replies View Related

How To Compare The Data And Highlight

Jun 13, 2014

Please refer to attached sheet. i have 2 sets of data. I need to compare the data and highlight.

First compare each cell in column W with each cell column L.

If match is found then compare the corresponding cell in column U with the value in column N.

Example:

W1 matches with L1 and U1 matches with N1 so do not highlight.

W2 matches with L7 and U2 does not match with N7 and hence highlight.

If value is different then highlight the cell in column U.

View 4 Replies View Related

VBA To Compare And Highlight Differences

Aug 3, 2013

I have to worksheets I get from two different dbases do the output is slightly different in each. What I need is simple, (I think). Just need to get a VBA that looks from Sheet1 to Sheet2 for a doc number. If it's not on Sheet 2, simply highlight it BLUE on Sheet, AND then looks from Sheet2 to Sheet1, and if it's not on Sheet1, then highlight the entry on Sheet2 GREEN.

I.E.
SHEET1SHEET2
COL ACOL A
11111AAAAA
22222BBBBB
3333311111
AAAAA44444

View 9 Replies View Related

Compare A List And Highlight Similarities

Feb 24, 2014

Is there a way I can compare the names in column A to those in both B&C and highlight them a certain color if they are the same?

View 3 Replies View Related

Compare Two Tables And Highlight Differences

Jan 30, 2012

I have two employee rosters, "yesterday" and "today", with same columns heading(First, Last, Location, Status, etc, etc, etc . I want to compare both rosters and highlight the differences on "today's" roster if an employee's information (location, status, etc) changes from yesterday.

Another change could be, an employee may not be on today's roster as he was yesterday and I could have a new employee on today's that wasn't on yesterday's. Is there a way to copy the row/record from yesterdays roster and add him to today's but highlight it so I know that he is gone?

Compare Two Sheets and Highlight Differences (Sheet attached)

View 1 Replies View Related

Vba: String Compare And Highlight The Difference

Aug 25, 2008

I have a string compare function that compares two strings and return 1 if matches. Each string has multiple words separated by ",", and all these words need to be matched.

This function is done, but I want to add a little feature to it. For each string, if one word cannot be found in the second string, change that word's size to 14, fontstyle to bold.

Function StringCompare(value1 As String, value2 As String) As Integer
''this function compare the words from 2 strings
''each word is seperated by "," and the order of these words does not matter
''return 1 if matches, 0 if not match
Dim v As Variant, u As Variant
Dim i As Integer
Dim answer As Integer, answer1 As Integer, answer2 As Integer
answer1 = 1
answer2 = 1

View 9 Replies View Related

VBA Compare Two Strings, Highlight Differences

May 18, 2009

how can I get the red highlighting to work like so?

Test Test_002 6698F ES6698F DVP3142 DVP3144 GTM8800 GTI8000 SDV394 SV384STC

View 9 Replies View Related

Compare Lists & Highlight Duplicates

Jul 3, 2007

I receive a daily spreadsheet which contains (amongst other information) a column of postcodes. I'd like to run a macro which compares these postcodes with a list of other postcodes, and then to colour highlight each instance of that post code in the daily spreadhseet.

I have been able to find and modify a macro but that searches for one post code at a time and then adds a comments box to the cell.

Instead of copying that piece of code and editing it for the several hundred post codes I need to "look up" against, I'd like to know how to create a list of post codes in the macro, so that the macro searches the first post code in the lookup list, finds and highlights each cell instance, then moves onto the next post code in the lookup list, until the end of the lookup list. I would like this as the editing of the lookup list would be much easier than editing paragraphs of code.

View 9 Replies View Related

Compare Data In Two Workbooks And Highlight Matches

Jan 28, 2008

Here is the scenario. I am running a business and each day I download my orders for the day. The information includes customer names and addresses. The customer name is for example in column E. We will call this "Book1".

I have a second workbook that I use to store bad customer information. i.e. customers that I do not wish to sell to. The information stored here is customer name and address e.g. column A - customer name, column B - 1st line of address etc. This will be "Book2".

I wish to programmatically be able to compare customer names in column E Book1 to the customer names stored in column A Book2. Any matches found should highlight the rows (i.e. orders) in Book1.

I have tried to make this clear as possible. I want to be able to do this as a macro hence programmatically so when I click a button this comparison/highlighting takes place.

View 4 Replies View Related

VBA - Compare 2 Columns Highlight Data Not Common To Both

May 9, 2014

Looking for some code to do a simple compare column A to Column B (row 1 contains headings) and highlight any differences.

View 3 Replies View Related

Macro For Compare Worksheet Highlight Difference

Sep 29, 2013

I have 1 workbook contains 2 Sheet with Name and Address in Column A,B,C,D. Sheet1 always contains New Data of Name and Address and its import from another software.

I need a macro to Compare these 2 sheets, Highlight the rows which is not in sheet2

View 5 Replies View Related

To Compare Details In 2 Workbook And Highlight If There Is Repetition

Mar 8, 2007

If i have a string of information in 2 workbook and I need to check if the details in column a in workbook 1 has a duplicate entry in column b in workbook 2 and if there is a duplication, then highlight it in both workbook. How can I go about it to create the codes so that I will be able to use it for different workbooks without changing the codes every now & then?

Scenario: -
Monthly invoice verifications for a few vendors.
To verify info from the manual invoice againts the auto invoice (2 different workbook for each vendor)
Need to verify info from Column A in Manual invoice against column B in Auto invoice. If there is a same data in both column, then data to be highlighted.

View 2 Replies View Related

Compare 2 Columns To Another Sheet & Highlight Matches

Dec 23, 2007

I have a workbook with two sheets imported from different sources. Sheet 1 is an AdHoc query from an Oracle 10g based program. Sheet 2 (PBIC 8 in my file) is a report generated from a property accountability program. The information from Sheet 2 is manually inputted into the Oracle program that generated Sheet 1.

I would like to have a macro that would compare the cell contents in columns "RegistrationNbr" (column S) and "SerialNbr" (column T) with the cell contents in Sheet 2 (PBIC 8). The match in Sheet 2 could be an identical match or part of a longer string. The matching cells in both sheets should be highlighted.

One thing to be considered is the column names in Sheet 1 will always be the same but the column letter designation may change base on the fields selected in the AdHoc query.

I didn't realize my file was too large and didn't upload.

View 4 Replies View Related







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