问题描述
干杯，大家好！表达下面的代码对我来说有点复杂，但是我会试一试.下面的代码应该执行以下操作:
Cheers, everyone! It is a bit complicated to me to express the code below, but i will give it a shot. The code bellow is supposed to do the following things:
1.从 D，E，H 和 M 列中检查范围. D，E 和 H 列在其范围内具有相似的值，即: D5 = V 和 D6 = V ； E5 = B 和 E6 = B ； H5 = A 和 H6 = A ，而 M 列具有数字作为值，即 M5 = 40 和 M6 = 70 .
1.Check the range from columns D, E, H and M. Columns D, E and H have similar values in their range i.e.: D5 = V and D6 = V ; E5 = B and E6 = B ; H5 = A and H6 = A, while column M has digits as values i.e. M5 = 40 and M6 =70.
2.M列(表示Range(m5:m50)必须将M5和M6中的值相加:40 +70.之所以将其相加是因为range( D5:D6，E5:E6，H5:H6 )在其列中具有相似的值.只有在其他所有列都提到( D，E和H )在其范围内具有相似的值，将其作为键( D5& E5& H5 = VBA； D6& E6& H6 = VBA ).如果
2.Column M (meaning Range(m5:m50) must sum up the values from M5 and M6: 40 + 70. The reason why it sums up is because range(D5:D6, E5:E6, H5:H6) have similar values in their columns. Only then the sumfunction must kick in range("m5:m50"), when all the other columns mentioned (D, E and H) have similar values in their range. Take it as key (D5&E5&H5 = VBA ; D6&E6&H6 = VBA). Both are similar. Then, If
3.范围("m5:m50")中的这2个值为> 100 ，两个单元格( M5 和 M6 )都变为红色).否则，将不采取任何措施.
3.Those 2 values from range("m5:m50") is > 100, both cells (M5 and M6 turn red). Otherwise, no action is taken.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Target
If (cell.Range("d5:d50").Value) & (cell.Range("e5:e50").Value) & (cell.Range("h5:h50").Value) Then
Sum1 = Application.WorksheetFunction.Sum(cell.Range("m5:m50"))
If Sum1 > 100 Then
cell.Range("m5:m50").Interior.Color = RGB(255, 0, 0)
Else
cell.Range("m5:m50").Interior.Color = RGB(255, 255, 255)
End If
End If
Next
Application.EnableEvents = True
End Sub
我的代码似乎不起作用，但是我也没有得到任何错误.如果有人能帮助我解决我的问题，我将不胜感激.预先感谢！
My code doesn't seem to work, but I do not get also any error. I would really appreciate if someone can help me to fix my problem as I am out of ideas. Thanks in advance!
推荐答案
如果我的理解是正确的，我建议对您的代码进行这种修改
if my understanding is correct, I propose this adaptation to your code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, sum1 As Long, k As Long, c(50) As Long
Application.EnableEvents = False
For i = 5 To 49
sum1 = 0
k = 0
For j = i + 1 To 50
If Cells(i, 4) = Cells(j, 4) And Cells(i, 5) = Cells(j, 5) And Cells(i, 8) = Cells(j, 8) Then
If sum1 = 0 Then sum1 = Cells(i, 13): k = 1: c(k) = i: Cells(i, 13).Interior.Color = RGB(255, 255, 255)
sum1 = sum1 + Cells(j, 13)
k = k + 1
c(k) = j
Cells(j, 13).Interior.Color = RGB(255, 255, 255)
End If
Next j
If sum1 > 100 Then
For j = 1 To k
Cells(c(j), 13).Interior.Color = RGB(255, 0, 0)
Next j
End If
Next i
Application.EnableEvents = True
End Sub
更强大的代码版本
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, sum1 As Long, k As Long, c(50) As Long
Application.EnableEvents = False
Range("M5:M50").Interior.Color = RGB(255, 255, 255)
For i = 5 To 49
k = 0
For j = i + 1 To 50
If Cells.Interior.Color <> RGB(255, 0, 0) Then
If Cells(i, 4) & Cells(i, 5) & Cells(i, 8) <> "" Then
If Cells(i, 4) = Cells(j, 4) And Cells(i, 5) = Cells(j, 5) And Cells(i, 8) = Cells(j, 8) Then
If k = 0 Then sum1 = Cells(i, 13): k = 1: c(k) = i
sum1 = sum1 + Cells(j, 13)
k = k + 1
c(k) = j
End If
End If
End If
Next j
If sum1 > 100 Then
For j = 1 To k
Cells(c(j), 13).Interior.Color = RGB(255, 0, 0)
Next j
End If
Next i
Application.EnableEvents = True
End Sub
这篇关于范围内的相似值使其成为KEY和求和函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！