如何在3个不同的工作表中比较3个不同的数据列，并在excel 2007中突出显示？ [英] how can I compare 3 different columns of data in 3 different worksheets and highlight them in excel 2007?

问题描述

所以我有两个数据工作表，我想比较工作表1的A列到工作表2的A列。工作表1是一个模板，工作表2是数据报告。对于两个工作表的列A是项目＃，工作表1更新项目＃和工作表2中的报表中的数据。我想创建一个公式或我猜vba代码（我对VBA一无所知），无论我能做什么，将比较项目＃秒。我想要公式/代码将工作表2中的报表中的数据拉到工作表1的模板中的相应项目＃，并且高亮起工作表2中不在的报表（A列）中的任何新项目＃工作表1，然后将这些新的高亮项目及其数据添加到工作表1.

工作表1的列A中的数据范围小于我试图在工作表2中创建一个IF公式来显示哪个项目号存在，哪个是是新的，但是由于工作表1的列A中的数据范围小于工作表2的列A，我终于得到一个错误值，我认为创建一个vba可能会更好，但不确定。任何建议，请一步一步的细节/形象做什么。到目前为止一般的模板我有一个vlookup拉数据，但我需要知道哪些项目是新的，并突出显示/拉他们。此外，如果我可以删除或标记停产的项目数字将是伟大的。我认为到目前为止，当我做一个vlookup，在工作表1而不是工作表2的项目出现＃N / A是停产项目。我有excel 2007，希望你能帮忙。

Sample Sheet1和Sheet2

使用 Vlookup 更新后的输出

我试图在三个工作表中比较三个不同的数据列，并突出显示差异使用VBA。我对VBA很新，我对编程不太了解。到目前为止，这是我所做的：

workheet1 ：

  Sub compare_cols（）
 Dim myRng As Range 
 Dim lastCell As Long 
'获取最后一行
 Dim lastRow As整数
 lastRow = ActiveSheet.UsedRange.Rows.Count 
'Debug.PrintLast Row is& lastRow 
 Dim c As Range 
 Dim d As Range 
 Application.ScreenUpdating = False 
对于每个c在Worksheets（worksheet1）。Range（A2：A& lastRow）.Cells 
对于每个d在工作表（worksheet2）。Range（A2：A& lastRow）.Cells 
 c.Interior.Color = vbGreen 
 If（InStr （1，d，c，1）> 0）然后
 c.Interior.Color = vbWhite 
退出
结束如果
下一个
下一个
对于每个c in Worksheets（worksheet2）。Range（A2：A& lastRow）.Cells 
 For each d In Worksheets（worksheet1）。Range（A2：A& lastRow ）.Cells 
 c.Interior.Color = vbYellow 
 If（InStr（1，d，c，1）> 0）然后
 c.Interior.Color = vbWhite 
退出对于
结束如果
下一个
下一个
 Application.ScreenUpdating = True 
 End Sub 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 End Sub 
工作表2：
 Sub compare_cols（）
 Dim myRng As Range 
 Dim lastCell As Long 
'获取最后一行
 Dim lastRow As Intege r 
 lastRow = ActiveSheet.UsedRange.Rows.Count 
'Debug.PrintLast Row is& lastRow 
 Dim c As Range 
 Dim d As Range 
 Application.ScreenUpdating = False 
 For each c In Worksheets（worksheet2）。Range（A2：A& lastRow）.Cells 
对于每个d在工作表（worksheet3）。Range（A2：A& lastRow）.Cells 
 c.Font.Color = rgbRed 
 If（InStr （1，d，c，1）> 0）然后
 c.Font.Color = rgbBlack 
退出
结束如果
下一个
下一个
对于每个c in Worksheets（worksheet3）。Range（A2：A& lastRow）.Cells 
 For each d In Worksheets（worksheet2）。Range（A2：A& lastRow ）.Cells 
 c.Interior.Color = vbRed 
 c.Font.Color = rgbWhite 
 If（InStr（1，d，c，1）> 0）Then 
 c.Interior.Color = vbWhite 
 c.Font.Color = rgbBlack 
退出
结束如果
下一个
下一个
 Application.ScreenUpdating = True 
 End Sub 
  

。

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 End Sub 
  

我遇到的问题：

• 在比较 worksheet1 和 worksheet2 ，不在 worksheet2 中的数据在 worksheet1 ，而 worksheet1 中的数据在 worksheet2 中突出显示为黄色。


• 例如 worksheet2 中的一些数据以黄色突出显示，但位于 worksheet1 中，其中应该不会发生然后在 worksheet2 和 worksheet3 中比较 worksheet3 在 worksheet2 中有一个红色的字体，而不在 worksheet2 中的项目以红色突出显示为白色字体，在 worksheet3 。


• worksheet2 中的数据，例如有一个红色彩色字体，但在 worksheet3 中找到，不应该发生。

你能告诉我为什么我的VBA代码不工作，还是我还能做什么？

解决方案

将条件格式化为您的需要而工作？这是一种突出出现在另一个范围内的单元格的方法（或者如果您在混合中抛出不（），则不会出现。

http：/ /www.techrepublic.com/blog/microsoft-office/use-conditional-formatting-to-compare-lists-in-excel/

我希望它有帮助！

JSR

so I have two worksheets with data, I want to compare column A of worksheet 1 to column A of worksheet 2 . Worksheet 1 is a template and worksheet 2 is a data report. Column A for both worksheets are item #s, worksheet 1 updates item #s and data from the report in worksheet 2. I want to create a formula or I guess vba code (I know nothing about VBA) whatever I can that will compare the item#s. I want the formula/code to pull the data from the report in worksheet 2 to the corresponding item #s in the template of worksheet 1 and high light any new item #s in the report(column A) of worksheet 2 that are not in worksheet 1 and then add those new high lighted item #s and its data to worksheet 1.

The range of data in column A of worksheet 1 is less than the range of data in column A of worksheet 2 that im trying to compare and pull from.

I thought about creating a IF formula in worksheet 2 to show me which item number exist, which are new but because the range of data in column A of worksheet 1 is less than column A of worksheet 2 I end getting an error value, I think creating a vba may be better but im not sure . Any advice, please give step by step detail/image of what to do. So far in general for the template I have a vlookup to pull the data BUT I need to know which items are new and highlight/pull them. Also if I could either delete or label the items numbers that is discontinued as discontinued that would be great. I think so far when I do a vlookup the items that are in worksheet 1 and not worksheet 2 come up as #N/A are the discontinued items. I have excel 2007, I hope you can help.

Sample Sheet1 and Sheet2

Output after updating using Vlookup

I am trying to compare three different columns of data in three worksheets and highlight the differences using VBA. I am very new to VBA and I don't know a lot about programming. So far this is what I've done:

worksheet1:

Sub compare_cols()
 Dim myRng As Range
 Dim lastCell As Long
 'Get the last row
 Dim lastRow As Integer
 lastRow = ActiveSheet.UsedRange.Rows.Count
 'Debug.Print "Last Row is " & lastRow
 Dim c As Range
 Dim d As Range
 Application.ScreenUpdating = False
 For Each c In Worksheets("worksheet1").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbGreen
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 Exit For
 End If
 Next
 Next
 For Each c In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet1").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbYellow
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 Exit For
 End If
 Next
 Next
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Worksheet 2:
Sub compare_cols()
 Dim myRng As Range
 Dim lastCell As Long
 'Get the last row
 Dim lastRow As Integer
 lastRow = ActiveSheet.UsedRange.Rows.Count
 'Debug.Print "Last Row is " & lastRow
 Dim c As Range
 Dim d As Range
 Application.ScreenUpdating = False
 For Each c In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet3").Range("A2:A" & lastRow).Cells
 c.Font.Color = rgbRed
 If (InStr(1, d, c, 1) > 0) Then
 c.Font.Color = rgbBlack
 Exit For
 End If
 Next
 Next
 For Each c In Worksheets("worksheet3").Range("A2:A" & lastRow).Cells
 For Each d In Worksheets("worksheet2").Range("A2:A" & lastRow).Cells
 c.Interior.Color = vbRed
 c.Font.Color = rgbWhite
 If (InStr(1, d, c, 1) > 0) Then
 c.Interior.Color = vbWhite
 c.Font.Color = rgbBlack
 Exit For
 End If
 Next
 Next
Application.ScreenUpdating = True
End Sub  

.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub  

Problems I'm having:

• in comparing worksheet1 and worksheet2, data that is not in worksheet2 are highlighted green in worksheet1, and data that is not in worksheet1 are highlighted yellow in worksheet2.
• some of the data in worksheet2 for example are highlighted in yellow but are found in worksheet1, which shouldn’t happen. Then in comparing worksheet2 and worksheet3, items that are not in worksheet3 have a red colored font, in worksheet2, and items that are not in worksheet2 are highlighted red with a white font, in worksheet3.
• data in worksheet2 for example have a red colored font but are found in worksheet3, which shouldn’t happen.

Can you please tell me why my VBA code isn't working, or what I else I can do?

解决方案

Would conditional formatting work for your needs? Here's a way to highlight cells that appear in another range (or don't appear, if you throw a "not(" in the mix).

http://www.techrepublic.com/blog/microsoft-office/use-conditional-formatting-to-compare-lists-in-excel/

I hope it helps!

JSR

这篇关于如何在3个不同的工作表中比较3个不同的数据列，并在excel 2007中突出显示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！