I have 2 worksheets - one is a monthly update, the other is last month's data.
I want to highlight the changes on the monthly update sheet using conditional formatting.
I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)
What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?
I would like to compare each cell in a spreadsheet to the cell on its right.
If the numeric value of a cell is less than the numeric value of cell on its right, I would like to highlight the cell on the right light red.
Maybe a better way of putting that is if the value of a cell is greater than the cell on its left, highlight it light red (The problem I foresee with this formulation is in the first row where there are no cells to the left).
these are two tables i have to compare.Sl No
Type
1
NORMAL
2
NORMAL
3
NORMAL
4
QUAD
[Code] ........
Sl No
Type number
1
1
2
1
[Code] ........
where,
1=NORMAL
2=QUAD
3=BI-DIRECTION
i want to compare between these two table based on the condition writen in RED. IF there is a mismatch i want to highlight the values in the first table.
I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?
View 2 Replies View RelatedIs it possible to perform conditional formatting on numerous worksheets at one time. I have about 50 worksheets that I would like to perform the same conditional format on. Is there a way to do this.
View 5 Replies View RelatedI have 2 worksheets. One has locations with numbers, example:
275 Location 1
276 Location 2
I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
271 275
272 300
I have 2 scenarios I need help with.
1st:
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red
2nd:
If the number on sheet 2 matches any number on sheet 1, highlight green
I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
have been trying to write a VB macro to compare two spreadsheets. Here is what I need help achieving....
Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.
If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.
If unique ID (with row of data) exists in Sheet2 and NOT in Sheet1, add row from Sheet2 to Sheet 3 and mark "ADD" in new column next to row.
If unique ID (with row of data) exists in both Sheet1 and Sheet2, take row from Sheet2 and place in Sheet3 and mark "UPDATE" in new column next to row.
Worksheet 1 has 200 rows, Worksheet 2 has 500 rows. Common denomentor in column A. Please advise how I can get 200 rows from w/s 1 pulling the relevant data from both worksheets into a new worksheet
View 9 Replies View RelatedI currently have 2 worksheets worth of data in the same workbook. Each data set has its own date and time stamp in columns A and B respectively with varying data then following in the row. I would like to write a macro that would look in worksheet 2 and find the matching date and time stamped row in worksheet 3. Then select the data from both worksheet 2 and worksheet 3 and paste them in the same row in worksheet 1.
View 9 Replies View RelatedI have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries.
However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.
How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?
I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.
I know that there has been similar requests on this forum (I searched and read them) but thew were different.
I would like to compare the data between 2 worksheets ("Sheet1") and ("Test") so that the contents of any cells in "Sheet1" that are different to the corresponding cells in "Test" are highlighted with a yellow background.
View 5 Replies View RelatedI have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries. Fine.
However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.
How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?
I have a user that needs to compare MSExcel files with a mass number of worksheets within the file/files. Is there anything to compare Excel files and to print out the compared results?
View 3 Replies View RelatedI have a task that I thought would be quite simple but I can’t find a solution in any of the books I have or on this forum.
I receive a list of companies each day who owe money, in Worksheet1. The next day I receive an updated list in Worksheet2. Those companies who have now paid have been deleted from the list. I want to compare the two lists and highlight those companies in Worksheet1 who are no longer in Worksheet 2.
I’ve tried to do this by getting the values in the list in Worksheet2 to loop through the list in Worksheet1 but can’t make it happen.
I’d be really grateful if someone was able to help me with the code I need.
In my attached example you can see I have Sheet "Computer" & Sheet "RHN" both have a number of columns, but all I want is to compare Column A of "Computer" with Column A of "RHN" and copy all entires found in A "Computer" and not found in A "RHN" to a new sheet!
View 4 Replies View RelatedI am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can´t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.
I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):
Sub find_cell()
Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate
ar = ActiveCell.Row
ac = ActiveCell.Column
' the two lines above store the row and column values of the cell found
' in your example ar is going to be equal to 229 and ac is 3
' now this following lines are to change the colors to red
With Cells(ar, ac).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub
I don´t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.
I have a file with 2 worksheets:
The "On" worksheet represents ALL of the students that LIVE on campus (freshman, sop****re, juniors, seniors)
The "JS" worksheet represents all the juniors and seniors going to school here
I need to know which of the JS live OFF campus.
So.....I need a macro that will compare the JS worksheet to the On worksheet and create a new worksheet called Off and populate it with those JS that are not in the On worksheet.
The "On" worksheet contains:
A B
Last First
The "JS" worksheet contains:
A B C D E F G
Last First Street Street2 City State NY
The new "Off" worksheet should contain the same columns as "JS"
I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
(Core starting issue solved but other threads were created to continue with trouble spots.)
I'm terrified to use VBA just yet, so right now I'm determined to highlight values in my spreadsheet just using conditional formatting.
I have 5 plus 1 columns of data. For example,
A B C D E....F
3 2 4 5 7....2
4 6 1 3 4....1
4 7 4 5 8....4
2 1 5 3 9....1
I successfully applied a MIN formula to compare between columns on each
row and display in F the MIN value. Notice each cell going down F has the
minimum value in analyzing across each row.
Now, I *also* want to apply a formula on the whole F column to compare these resultant MIN values
and give me the MINIMUM value between those as well, so now I'm comparing across
rows. Got it? Now then.....I want the ENTIRE row that has this MIN value in F to be highlighted, because that will be the best choice for the application of what I'm doing. Did you understand this paragraph. It might have been confusing, but I think I said everything correctly, so read it carefully.
I've tried all sorts of "Formula is", "Cell value is", dollar sign this, dollar sign that, no dollar sign this, <, >, =, highlighted ALL the block of data, highlighting JUST the column across the rows of which will be compared, only highlighted one cell, but nothing has worked.
Is my problem that you can't have 2 formulas in one cell? So then I thought I should ADD another condition, but nothing works.
Let me throw in one more element that I did not state. If anywhere down the F column there is a 0, I don't want that to be my minimum. I want it to evaluate and give me the minimum value greater than 0.
Please read everything I wrote carefully. It might be confusing, but I hope someone has an answer, because it sure has frustrated me.
Now, I'm thinking that VBA programming may give me answers, but I am so terrified of macros, I don't know how to begin that. I have this impression based on the many sites I've looked at on the internet that many people do macros and it's quite easy, but it's funny to me that I am so scared. There's something about me freaking out that I won't have a period or a quote in the right place and I'll stress my day away over it.
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....
I'm trying to compare 2 worksheets with the same headers(NAME, ADDRESS, CONTACT NO...). What codes should I use that when there is a duplicated row in worksheet A and worksheet B, it will be deleted and when there are 2 names with different addresses and/or contact no, the whole row in file A will be obtained. Output should also be in another worksheet. It goes like this.
worksheet A
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456
[Code]...
So the output should be:
worksheet C
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456
JIM JKL 345
SAM FGH 789
Is this possible in vba?
Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..
View 1 Replies View RelatedI am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.
View 3 Replies View RelatedA little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View Related