Comparing text strings

**gmalpani** · 12-15-2011, 08:57 AM

I have to do a simple operation from macro that it compares text in three columns and gives results using following logic in adjacent column :-

Column --> G H I ---> J
FC FC FC ---> FC
NC NC NC ---> NC
Else

result in J should be PC.

Also, if F = FC, color cell with Green, If F=NC, color cell Red and else if its PC color cell Blue.
I tried to write a code and but while compilation it shows some error. I think there is some error in the code lines where I have applied "And" operation.
Could any one help me in correcting the code.

Thanks is advance !!

LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For RowNo = 2 To LastRow
        If Cells(RowNo, "G") And Cells(RowNo, "H") And Cells(RowNo, "I") = "FC" Then
            strCompliance = "FC"
        ElseIf Cells(RowNo, "G") And Cells(RowNo, "H") And Cells(RowNo, "I") = "NC" Then
            strCompliance = "NC"
        Else
            strCompliance = "PC"
        End If
        
        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(0, 0, 255)
            End If
        End With

**Special-K** · 12-15-2011, 09:49 AM

The code you've supplied produces a Next Without For error
Put a Next after the EndWith

Also try this in your IF...ANDs...
seems to work

        If Cells(RowNo, "G") = "FC" And Cells(RowNo, "H") = "FC" And Cells(RowNo, "I") = "FC" Then
            strCompliance = "FC"
        ElseIf Cells(RowNo, "G") = "NC" And Cells(RowNo, "H") = "NC" And Cells(RowNo, "I") = "NC" Then
            strCompliance = "NC"
        Else
            strCompliance = "PC"
        End If

**gmalpani** · 12-18-2011, 11:09 AM

Hi Special-K,

Thanks for your reply !!

I am facing one more challenge here, if one of the cell is empty then the results are PC.
For e.g. in column G, I don't have any value as the compliancy is not applicable for this product but in other two product compliancy is provided.

Illustration:
Column G --- no value
Column H --- FC
Column I --- FC

Then, G should be ignored and only H and I should be compared.

Can you give me a hint, what could be the most optimized logic for this. Also, do we have any Compare function in VBA.

Br,
gmalpani

**gmalpani** · 12-18-2011, 12:59 PM

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