在Excel 2010中,比较来自列的数据和突出显示值(如果使用宏和VBA不同) [英] In Excel 2010 compare data from columns and highlight values if different using macro and VBA
问题描述
我们可以找到一个列表在sheet2中说50名工匠,他们可以分配到第一张表中的多个任务(可能有数百个任务),因此列的长度不会相同。
当我们运行Marco时,我们希望任何具有值(一页1)的单元格与第二页中的值不匹配,以红色突出显示,并替换文本其中包含不正确的名称的声明
我有一些代码,通过本网站或其他搜索之一找到,并修改了它,所以它接近于什么我想要。但它突出显示数据错误的方式,突出显示匹配的值,我想要不符合的值被突出显示!我已经尝试过,但一直无法纠正这个 - 任何人都可以帮忙,还要整理代码?
Sub CompareAndHighlight()
Dim rng1 As Range,rng2 As Range,i As Integer,j As Integer
For i = 1 To Sheets(workorders)。Range(U& Rows.Count).End(xlUp).Row
设置rng1 = Sheets(workorders)。范围(U& i)
对于j = 1 To Sheets(craftspersondata)。范围(A& j)
如果StrComp(Trim( rng1.Text),修剪(rng2.Text),vbTextCompare)= 0然后
rng1.Interior.Color = RGB(255,0,0)
rng1.Value =不正确的名称
结束如果
设置rng2 =没有
下一步j
设置rng1 =没有
下一个i
End Sub
我尝试更改以下行:
<如果Str Comp(Trim(rng1.Text),Trim(rng2.Text),vbTextCompare)= 0然后
to:
如果StrComp(Trim(rng1.Text ),Trim(rng2.Text),vbTextCompare)<> 0然后
但这突出显示列中的每一行,所以不会给我需要的修复...
尝试使用以下代码:
Sub CompareAndHighlight()
Dim rng1 As Range,rng2 As Range,i As Integer,j As Integer
Dim isMatch As Boolean
For i = 2 To Sheets(workorders)。Range(U& Rows.Count).End(xlUp).Row
isMatch = False
设置rng1 = Sheets(workorders)。Range(U& i)
对于j = 1 To表格(craftspersondata)。范围(A& Rows.Count).End(xlUp).Row
设置rng2 =表格(craftspersondata)。范围(A& j)
如果StrComp(Trim(rng1.Text),Trim(rng2.Text),vbTextCompare)= 0然后
isMatch = True
退出
结束如果
设置rng2 =没有
下一步j
如果不是isMatch然后
rng1.Interior.Color = RGB(255,0,0)
rng1.Value =不正确的名称
结束如果
设置rng1 =没有
下一个i
结束Sub
I have two columns, from 2 worksheets. In the first worksheet a column contains a list of craftspeople assigned to a task. The second worksheet contains just a list of the craftspeople. I need to compare the craftspeople from the first worksheet and highlight a cell if its value does not match any value in the second worksheet.
We could end up with a list of say 50 craftspeople in sheet2 and they could be assigned to multiple tasks in the first sheet (there could be hundreds of tasks), so the columns will not be the same length.
When we run the Marco we want any cells with a value (one sheet 1) that does not match the values in the second sheet to highlight in red and replace the text with the statement "Incorrect Name"
I have some code which I found via one of the searches on this site or another, and have modified it, so it’s close to what I want. But it highlights the data the wrong way round, its highlighting the values which match, I want the ones that do not match to be highlighted! I've tried but have been unable to correct this - can anyone help, and also tidy up the code???
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
For i = 1 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("workorders").Range("U" & i)
For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("craftspersondata").Range("A" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Interior.Color = RGB(255, 0, 0)
rng1.Value = "Incorrect Name"
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
End Sub
I've tried changing the following line:
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
to: If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) <> 0 Then
but this highlights every row in the column, so doesn't give ne the fix I need...
Try to use following code:
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
Dim isMatch As Boolean
For i = 2 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
isMatch = False
Set rng1 = Sheets("workorders").Range("U" & i)
For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("craftspersondata").Range("A" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
isMatch = True
Exit For
End If
Set rng2 = Nothing
Next j
If Not isMatch Then
rng1.Interior.Color = RGB(255, 0, 0)
rng1.Value = "Incorrect Name"
End If
Set rng1 = Nothing
Next i
End Sub
这篇关于在Excel 2010中,比较来自列的数据和突出显示值(如果使用宏和VBA不同)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!