# Match Data on Two Sheets and See if Criteria is met

#### trevor2524

##### New Member
Hello, I'm trying to do a big macro and have a step for it that I was trying to break down in pieces.

Step 1: Check Column C in Sheet1 for color values in the cell. If it has a color value in a cell take the actual value and see if it exists in Column A on Sheet2.

Step 2: When the value is found it will check Column I for a value of "Yes"

Step 3: If no value is found then it will check to see if the date in Column D matches the date in Column F on sheet1. If it does then on Sheet2 Column H will receive the Value of "Yes"

If these conditions are not met it will then move onto the next color value cell on sheet1. This is part of a bigger macro so I'm trying to break it down.

Thanks for any help given.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Code:
``````Sub colorValueCheck()

Dim sOne As Worksheet
Dim sTwo As Worksheet

Set sOne = ThisWorkbook.Sheets("Sheet1") ' CHANGE ME
Set sTwo = ThisWorkbook.Sheets("Sheet2") ' CHANGE ME

Dim startRow1 As Integer
Dim endRow1 As Long

startRow1 = 2
endRow1 = sOne.Cells(Rows.Count, "C").End(xlUp).Row

Dim startRow2 As Integer
Dim endRow2 As Long

startRow2 = 2
endRow2 = sTwo.Cells(Rows.Count, "A").End(xlUp).Row

For x = startRow1 To endRow1 Step 1

If sOne.Cells(x, 3).Interior.ColorIndex <> -4142 Then
For y = startRow2 To endRow2 Step 1
If sOne.Cells(x, 3).Value = sTwo.Cells(y, 1) Then
If sTwo.Cells(y, 9).Value <> "Yes" Then
If sOne.Cells(x, 6) = sTwo.Cells(y, 4) Then
sTwo.Cells(y, 8) = "Yes"
End If
End If
End If
Next y
End If
Next x

End Sub``````

Thank You that worked. Since you were able to help with that can I bother you for another portion of that macro. I have it broken down to 3 steps since its so big. It is for the If statement regarding Step 3 listed above.
"Step 3: If no value is found then it will check to see if the date in Column D matches the date in Column F on sheet1. If it does then on Sheet2 Column H will receive the Value of "Yes""

If the dates do not match then Column G will receive a "Yes".

And the final step would be regarding step 2 and it will probably be the hardest one.

Nevermind I figured that portion out. The last portion I have to figure out is the follow:

It is regarding for the other option for Step 2:

"Step 2: When the value is found it will check Column I for a value of "Yes""

If the Value of Column I = "Yes" then the program will check to see if the cell right below that one has a value of "Yes" if it does not it will check to see if there is a Value in Column D in that corresponding row. If there is it will proceed onto the Steps created above. If it doesn't then it will retrieve the value Found in Column F from Sheet1 that corresponds to this color value. It can do this a total of 3 times.

Replies
1
Views
116
Replies
5
Views
220
Replies
8
Views
195
Replies
13
Views
2K
Replies
4
Views
243

1,221,185
Messages
6,158,409
Members
451,490
Latest member
desktopace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back