highlight cells based on 2 cell values

[Craigh4444]

Good morning, i have written the VBA code below to move data from 1 sheet (Mastersheet) to another sheet (Audit Check 1) based on a column cell value and then highlight the blanks that occur in column F,

now i want write this code that will only highlight a cell if the value in column E matches the value i have in my code AND the value in column F is blank.

Private Sub CommandButton1_Click()
Sheets("Audit Check 1").Cells.Clear
Sheets("Audit Check 1").Cells.Interior.Color = xlNone
Dim i, Lastrow

Lastrow = Sheets("P6 Key Fields Layout").Range("A" & Rows.count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("P6 Key Fields Layout").Cells(i, "E").Value = ("PMS") Then
Sheets("P6 Key Fields Layout").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Audit Check 1").Range("A" & Rows.count).End(xlUp).Offset(1)
End If
Next i


Lastrow = Sheets("P6 Key Fields Layout").Range("A" & Rows.count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("P6 Key Fields Layout").Cells(i, "E").Value = ("SSR") Then
Sheets("P6 Key Fields Layout").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Audit Check 1").Range("A" & Rows.count).End(xlUp).Offset(1)
End If
Next i


Lastrow = Sheets("P6 Key Fields Layout").Range("A" & Rows.count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("P6 Key Fields Layout").Cells(i, "E").Value = ("HSR") Then
Sheets("P6 Key Fields Layout").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Audit Check 1").Range("A" & Rows.count).End(xlUp).Offset(1)
End If
Next i

Dim r As Range
Set r = Sheets("Audit Check 1").Range("F:F")

For Each cell In r

If cell.Value = "" Then
cell.Interior.ColorIndex = 6

End If

Next

MsgBox ("Audit Check Complete")

End Sub

 

[Joe4]

I think it would go a long way in communicating what you are trying to do if you could show us an example of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.