Hello Siobahn,
I don't quite follow.
As per your opening post, you wanted:-
- When a home is sold and it has a client name in Column F (not "SPEC" or Sales Center" in Column F) and is at 45% then the relevant row of data is copied to sheet 2. This is covered by the following part of the code:-
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
- When a home is sold and it has a client name in Column F (not "SPEC" or "Sales Center" in Column F) and is at 70% then the relevant row of data is copied to sheet3. This is covered by the following part of the code:-
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
- When a home is sold and is at greater than or equal to (>=) 90% and Column F is not showing "SPEC" or "Sales Center" then the relevant row of data is high-lighted green on the main sheet. This is covered by the following part of the code:-
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
As for Column P (keys), I refer you to your comment in your opening post:-
Also, if the column for keys has not been marked as received, I want the row to be highlighted yellow on the master sheet (sheet1)
and my assumption in my post #1 which you did not clarify:-
I have interpreted this to mean any cell in Column P that is blank or has an "N" placed in it.
So I now assume that you mean only if a cell has "N" in Column P you would like the relevant row of data high-lighted yellow in the main sheet.
A slight alteration to line 29 in the code in my post #1 should fix this for you as follows:-
If Cells(i, 16).Value = "N" Then
Here is the code again with the slight modification:-
Option Compare Text
Sub TransferData()
Dim lr As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
End If
If Cells(i, 16).Value = "N" Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 6
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
and following is the link to your file with the modified code implemented. You will see that it works as per your original explanation:-
https://www.dropbox.com/s/03ncdbcmyqsotxs/Siobahn%28If%20block%20statement%29.xlsm?dl=0
I hope its all clear now for you.
Cheerio,
vcoolio,
Thank you so so much! It works perfectly!! I truly appreciate it!
Nice to hear from you again.
Is this it?
https://wetransfer.com/downloads/24b0c3336358f1d67ee7157cab64cbfb20210105021917/7d37c7
I had it in my personal file.
The old Drop Box file has been deleted. Free file sharing sites generally only allow one month "free" then delete them.
Let me know if you need any help.
Cheerio,
vcoolio.
The "not equal to" SPEC or SALES CENTER code does not seem to be registering. Everything at 90% or 100% is turning green and ANYTHING without an answer in the keys field is turning yellow.
I only want those conditions to apply to homes that are SOLD (meaning the buyer column does NOT say "Spec" of "Sales Center". I am using Microsoft Excel 2007 - could this be the issue?
Thank you!