I have 2 sheets (TC Sheet & AE Sheet) that I need to match. I need to sum some values in AE sheet to get a match in TC sheet. I store the values and addresses of identified cells from AE sheet to 2 separate arrays (one for values and one for address) and once it finds a match I want to color the cells of addresses stored in my array to blue. I want to put the code in
below.
VBA Code:
'color matched cells
VBA Code:
Sub test1()
'Set Sheets
Set aeRprt = Sheets("AE")
Set tcRprt = Sheets("TC")
Dim n As Long
Dim aerrayV() As Variant
Dim aerrayA() As String
Dim size1 As Long
Dim size2 As Long
Dim index1 As Long
Dim index2 As Long
Dim SaerrayV As Long
ReDim aerrayV(size1)
ReDim aerrayA(size2)
Dim total As Long
'Define last row count
aeRow = aeRprt.Cells(Rows.Count(), 1).End(xlUp).Row
tcRow = tcRprt.Cells(Rows.Count(), 1).End(xlUp).Row
'Loop to sum non-higlighted cells to match with TC Sheet within 3 days range
For d = 2 To tcRow
Set Search3 = tcRprt.Cells(d, 2)
Set Search4 = tcRprt.Cells(d, 3)
size1 = 1
size2 = 1
index1 = 0
index2 = 0
If Search3.Interior.Color = 16777215 Then
For e = 2 To aeRow
If aeRprt.Cells(e, 2).Interior.Color = 16777215 Then
If IsEmpty(aeRprt.Cells(e, 2).Value) = False Then
If aeRprt.Cells(e, 2).Value > 0 Then
If Search3 - aeRprt.Cells(e, 1) >= 0 And Search4 - aeRprt.Cells(e, 1) <= 3 Then
aerrayV(index1) = CDec(aeRprt.Cells(e, 2).Value)
aerrayA(index1) = aeRprt.Cells(e, 2).Address
size1 = size1 + 1
size2 = size2 + 1
ReDim Preserve aerrayV(size1)
ReDim Preserve aerrayA(size2)
index1 = index1 + 1
index2 = index2 + 1
SaerrayV = Application.WorksheetFunction.Sum(aerrayV)
If SaerrayV = Search3 Then
'color matched cells
End If
End If
End If
End If
End If
Next e
End If
Next d
End Sub