In column "G" and column"Q", IF, VVS1,VVS2, VS1, VS2,SI1, SI2, I1 are mentioned multiple times rendomly. Where IF, VVS1,VVS2, VS1, VS2,SI1, SI2, I1 are in orders like A to Z. in "G" and "Q" column both may and may not exctly match. I want to highlight bet

Question

In column "G" and column "Q", IF, VVS1,VVS2, VS1, VS2,SI1, SI2, I1 are mentioned multiple times randomly. Where IF, VVS1,VVS2, VS1, VS2,SI1, SI2, I1 are in orders like A to Z. in "G" and "Q" column both may and may not exactly match. I want to highlight better word in greater than order in green back ground and weaker in less than order in red back ground.

Answer 1

Hi @NIRAJ SHAH, 

Thank you for posting your question in the Microsoft Q&A forum. This is a very practical scenario, and VBA is a suitable and effective way to handle it. You can refer to the steps below to achieve the comparison and highlighting logic you described. 

• Press Alt + F11 to open the VBA Editor 
• Go to Insert > Module 

• Paste the following VBA code into the module window: 

Option Explicit
 
Function ClarityRank(ByVal s As String) As Long
    Dim arr
    arr = Array("IF", "VVS1", "VVS2", "VS1", "VS2", "SI1", "SI2", "I1")
    Dim i As Long
    ClarityRank = 0
    For i = LBound(arr) To UBound(arr)
        If UCase$(Trim$(s)) = arr(i) Then
            ClarityRank = i + 1
            Exit Function
        End If
    Next i
End Function
 
Sub ColorClarity_G_Q()
    Dim ws As Worksheet
    Dim lastRow As Long, r As Long
    Dim rgG As Range, rgQ As Range
    Dim rankG As Long, rankQ As Long
 
    Set ws = ActiveSheet         
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
 
    For r = 1 To lastRow          
        Set rgG = ws.Cells(r, "G")
        Set rgQ = ws.Cells(r, "Q")
 
        If rgG.Value <> "" And rgQ.Value <> "" Then
            rankG = ClarityRank(rgG.Value)
            rankQ = ClarityRank(rgQ.Value)
 
            
            rgG.Interior.ColorIndex = xlNone
            rgQ.Interior.ColorIndex = xlNone
 
            If rankG > 0 And rankQ > 0 Then
                                If rankG < rankQ Then
                    rgG.Interior.Color = vbGreen
                    rgQ.Interior.Color = vbRed
                ElseIf rankG > rankQ Then
                    rgG.Interior.Color = vbRed
                    rgQ.Interior.Color = vbGreen
                End If
            End If
        End If
    Next r
End Sub

 

• Close the VBA editor 
• Press Alt + F8, select ColorClarity_G_Q, then click Run 

Each time the data in columns G or Q changes, simply run the macro again to refresh the colors. If both values are equal, or if one of them is not a valid clarity grade, no color will be applied. 

I hope you’ll have a chance to try the solution shared above, and I genuinely hope it proves helpful for your specific scenario. 

If you have any updates, further questions, or would like to explore improvements or alternative approaches, please feel free to reply directly under this post. I’ll be happy to follow up and assist you further. 

Thank you so much for reaching out 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Answer 3

https://1drv.ms/x/c/d3383f1a55147ef2/IQBZuHLKIIZOQLire-J4vZKUAYKjTCPRsmK2msm_ecbX7OE?e=w0aDNl