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 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

NIRAJ SHAH 20

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.

0 comments

Answer accepted by question author

3 additional answers

Your answer

Answer 1

Hi @NIRAJ SHAH,

Thank you for your patience while I reviewed your latest notes and rechecked the workbook. Based on your description, I’ve updated the original VBA so it handles the full set of classifications in the intended order, and also accounts for #N/A values and blank cells. This should make the highlighting logic more reliable on your dataset.

Could you please try updating and running the macro with the steps below:

Press Alt + F8 to open the Macro dialog.
Select ColorClarity_G_Q and click Edit.

User's image

In the editor window that opens, replace the existing code with the updated version I shared.

Option Explicit
 
Function ClarityRank(ByVal s As Variant) As Long
    Dim arr As Variant
    Dim i As Long
    If IsEmpty(s) Or IsError(s) Or IsNull(s) Then
        ClarityRank = 0
        Exit Function
    End If
    On Error Resume Next
    s = UCase$(Trim$(CStr(s)))
    If Err.Number <> 0 Then
        ClarityRank = 0
        Err.Clear
        Exit Function
    End If
    On Error GoTo 0
    arr = Array("IF", "VVS1", "VVS2", "VS1", "VS2", "SI1", "SI2", "SI3", "I1", "I2", "I3")
    ClarityRank = 0
    For i = LBound(arr) To UBound(arr)
        If 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 = 2 To lastRow
        Set rgG = ws.Cells(r, "G")  ' Projected Clarity
        Set rgQ = ws.Cells(r, "Q")  ' Lab Result Clarity
        rgG.Interior.ColorIndex = xlNone
        rgQ.Interior.ColorIndex = xlNone
        If Not IsEmpty(rgG.Value) And Not IsEmpty(rgQ.Value) And _
           Not IsError(rgG.Value) And Not IsError(rgQ.Value) Then
            rankG = ClarityRank(rgG.Value)
            rankQ = ClarityRank(rgQ.Value)
            If rankG > 0 And rankQ > 0 Then
                If rankG < rankQ Then
                    rgG.Interior.Color = RGB(144, 238, 144)
                    rgQ.Interior.Color = RGB(255, 182, 193)
                ElseIf rankG > rankQ Then
                    rgG.Interior.Color = RGB(255, 182, 193)
                    rgQ.Interior.Color = RGB(144, 238, 144)
                End If
            End If
        End If
    Next r
End Sub

Close the editor window.
Return to the worksheet. Press Alt + F8, select ColorClarity_G_Q, and click Run.

User's image

I’ve tested this updated macro directly on your file, and it works as expected on my end. When you have a moment, please run through the steps above on your side and let me know the outcome.

User's image

Looking forward to your update!

Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-10T07:31:12.5733333+00:00

I hope you’re doing well, @NIRAJ SHAH!

I just wanted to check in have you had a chance to review the answer above and try it out yet? If it worked for your sheet, I’d really appreciate it if you could kindly click “Accept Answer” on the post.

If you’re still running into issues or if anything is unclear, please feel free to get back to me. I’m happy to take another look and adjust the solution as needed.

Thank you for your patience.

Answer 2

Rin-L 18,410 Microsoft External Staff Moderator

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

User's image

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

User's image

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.

Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-04T09:25:19.91+00:00

Hi @NIRAJ SHAH,

Is there any update from you? If yes, please feel free to let me know.
NIRAJ SHAH 20 Reputation points

2026-02-04T09:28:21.5066667+00:00

I don't understand how to apply the formula.
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-04T09:33:27.72+00:00

Could you please let me know which step you’re getting stuck on from the steps I outlined earlier, or explain a bit more where the confusion starts, @NIRAJ SHAH?
NIRAJ SHAH 20 Reputation points

2026-02-04T09:38:12.06+00:00

I don't understand how to apply the formula. There are multiple end there where it should be used and apply.
NIRAJ SHAH 20 Reputation points

2026-02-04T09:39:27.6133333+00:00

please mention step by step. I can only copy and paste what ever is mentioned. rest I'm unable to do.
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-04T09:50:15.66+00:00

Could you let me know whether you were able to insert the module successfully by pressing Alt + F11 to open the VBA Editor, then going to Insert > Module and pasting the code into that new module window? I’d like to understand if that part worked as expected or if you got stuck there.

After pasting the module, are you having trouble actually running it, or does it run but not produce the result you’re expecting?

Since you mentioned that there are multiple places where it’s unclear how or where the formula should be applied, it might be easiest if I can see your setup directly. If you’re comfortable doing so, please feel free to send me your Excel file via a private message. That will allow me to better understand the context you’re working in and give you much more precise guidance tailored to your sheet.

To access private messages, click the banner as shown in the image below:
NIRAJ SHAH 20 Reputation points

2026-02-04T10:30:43.52+00:00

RUN TIME ERROR "13":
TYPE MISMATCH
NIRAJ SHAH 20 Reputation points

2026-02-04T10:32:52.4233333+00:00

RUN TIME ERROR'13':
TYPE MISMATCH
NIRAJ SHAH 20 Reputation points

2026-02-10T07:41:47.1233333+00:00

AFTER COUPLE OF ATTEMPS IT SOLVE MY ISSUE AND UPDATE EXCEL.
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-10T07:46:04.2566667+00:00

Thank you very much for your patience and cooperation throughout this process, I truly appreciate the time and effort you put in while we worked through the troubleshooting steps together.
NIRAJ SHAH 20 Reputation points

2026-02-10T09:08:41.2633333+00:00

NOW WHEN I UPDATE DATA, IT DOESN'T UPDATE, WHAT TO DO TO UPDATE? and how to remove formula
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-10T09:15:06.2533333+00:00

Please try running the VBA macro again to refresh the highlight formatting (pressing Alt + F8 > select the macro > Run). This will recolor the cells based on the updated data you’ve just entered.

Regarding the part about removing the formula, could you clarify a bit for me: Are you finalizing the data and want to remove the formulas completely because you no longer need this feature?
NIRAJ SHAH 20 Reputation points

2026-02-10T12:06:36.41+00:00

I want to remove this color coding from excel which I have applied as it's little difficult for me to apply easily. It's confusing for me.
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-11T14:28:50.84+00:00

If you want to remove this color coding, you can simply select columns G and Q, then change the Fill Color back to white to restore the original look. Does this match what you intend to do?
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-12T14:30:53.5+00:00

I hope you’re feeling well today.

If you need anything else on this topic, please don’t hesitate to let me know. I’m here and I’ll gladly do my best to support you. Thank you so much for the time you’ve spent working with me and for your patience throughout the process. I truly appreciate it.
NIRAJ SHAH 20 Reputation points

2026-02-13T06:16:17.3966667+00:00

Thank you for guiding how to apply formula. I appreciate your kind support.
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-25T11:08:46.4533333+00:00

Thank you so much for your kind confirmation and cooperation. It means a lot.

Wishing you all the very best, and I genuinely hope I’ll have the chance to support you again in the future.

Answer 3

NIRAJ SHAH 20

[Moderator note: personal info removed]

0 comments

Answer 4

NIRAJ SHAH 20

User's image

NIRAJ SHAH 20 Reputation points

2026-02-04T10:33:38.4466667+00:00

CAN YOU DO WITH HELP OF ANYDESK?
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-05T08:54:06.62+00:00

Hi @NIRAJ SHAH,

Just to kindly clarify, as a forum moderator, I’m only able to communicate and provide support within this forum environment. Unfortunately, I don’t have permission to connect through external applications such as AnyDesk or similar remote tools.

Based on your description, I initially assumed a sample dataset and tested the VBA logic accordingly. However, it seems that this assumed structure doesn’t fully match the actual data in your worksheet, which is most likely the reason you’re encountering the issue.

To review this more accurately and avoid any further assumptions, I’d kindly suggest that you share the file with me via private message, as I mentioned earlier. This will allow me to look directly at the real data layout and adjust the approach accordingly to better meet your requirement.

I would truly appreciate it if you could kindly extend a bit more patience, as I’m making every effort to assist you and to understand the situation more clearly. Thank you very much.
NIRAJ SHAH 20 Reputation points

2026-02-05T08:59:33.83+00:00

how to share you file via private message?
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-06T10:05:20.1466667+00:00

You can click on the banner below to open a private message. From there, please attach your file and send it to me.

Alternatively, you may upload the file to OneDrive and share the link with me, whichever option is more convenient for you.
NIRAJ SHAH 20 Reputation points

2026-02-07T06:51:38.0366667+00:00

[Moderator note: personal info removed]
Rin-L 18,410 Reputation points Microsoft External Staff Moderator

2026-02-08T09:01:40.64+00:00

I’ve had a chance to review the Excel file you shared, and based on what I’m seeing, the type mismatch error seems to originate from Columns G and Q. In those columns, there are several values that fall outside the standard list you mentioned in your original question. For example, the file contains entries such as SI3, I2, and I3, which weren’t referenced earlier. I also noticed that some cells contain #N/A values or are left blank, and these can also contribute to the issue.

Could you please clarify whether those values are expected, and how they should be handled in your logic, or how they should be ordered or treated in relation to the original sequence you’re using?

I look forward to your clarification.
NIRAJ SHAH 20 Reputation points

2026-02-09T06:28:26.26+00:00

IT'S IN ORDER 1 to 10 LIKE IF, VVS1, VVS2, VS1, VS2, SI1, SI2, SI3, I1, I2, I3 OUT OF WHICH SOME ARE RARELY USED. #N/A REFERRENCE TO DATA WHICH IS NOT AVAILABLE AT PRESENT. BUT WILL BE UPDATED AS AND WHEN REQUIRED. "G" IS MY PROJECTED CLARITY OF A DIAMOND WHERE AS "Q" IS RESULT CLARITY WHICH I RECEIVED FROM LAB. THE DIFFERENCE BETWEEN BOTH I WANT TO MAKE HIGHLIGHT. BETTER ONE BY GREEN AND LOWER ONE BY RED BACKGROUND
NIRAJ SHAH 20 Reputation points

2026-02-09T06:39:08.1166667+00:00

[Moderator note: personal info removed]

everything is mentioned in sheet mentioned as 5047-5051

Share via

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

3 additional answers

Your answer