Hi,
Meanwhile I was trying to write a code and somehow end up with desired results.
But as my logic is not very strong, I end up with too many lines of codes.
Below is the code. Could some1 suggest me the more optimized code for the same. I have attached a file as well for your reference.
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowNo = 2 To LastRow
Select Case Cells(RowNo, "G")
Case "FC"
If (Cells(RowNo, "H") = "FC" Or Cells(RowNo, "H") = "") And (Cells(RowNo, "I") = "FC" Or Cells(RowNo, "I") = "") Then
strCompliance = "FC"
Else
strCompliance = "PC"
End If
Case "PC"
If (Cells(RowNo, "H") = "PC" Or Cells(RowNo, "H") = "") And (Cells(RowNo, "I") = "PC" Or Cells(RowNo, "I") = "") Then
strCompliance = "PC"
Else
strCompliance = "PC"
End If
Case "NC"
If (Cells(RowNo, "H") = "NC" Or Cells(RowNo, "H") = "") And (Cells(RowNo, "I") = "NC" Or Cells(RowNo, "I") = "") Then
strCompliance = "NC"
Else
strCompliance = "PC"
End If
Case ""
If Cells(RowNo, "H") <> "" Then
strCompliance = Cells(RowNo, "H")
If Cells(RowNo, "I") = strCompliance Then
strCompliance = Cells(RowNo, "H")
ElseIf Cells(RowNo, "I") = "" Then
strCompliance = Cells(RowNo, "H")
Else
strCompliance = "PC"
End If
ElseIf Cells(RowNo, "H") = "" Then
strCompliance = Cells(RowNo, "I")
End If
End Select
With Cells(RowNo, "J")
.Value = strCompliance
.Font.Bold = True
If Cells(RowNo, "J") = "FC" Then
Cells(RowNo, "J").Interior.Color = RGB(0, 255, 0)
ElseIf Cells(RowNo, "J") = "NC" Then
Cells(RowNo, "J").Interior.Color = RGB(255, 0, 0)
ElseIf Cells(RowNo, "J") = "PC" Then
Cells(RowNo, "J").Interior.Color = RGB(255, 255, 153)
ElseIf Cells(RowNo, "J") = "" Then
Cells(RowNo, "J").Interior.Color = RGB(125, 125, 125)
End If
End With
Bookmarks