none
Excel color matching rows RRS feed

  • Question

  • Hello Community,

    I have found many many great help here reading you.

    This time i need your help because i can't apply any of the solution i have read.

    i export to excel from datagridview.

    i want to color rows if condition are met.

    i can do it with 1 parameter. i'm stuck when i want to add a second condition.

    Code working right now :

     For Each Cell1 In Wb.Worksheets("Sheet1").Range("D2:D100") 
                For Each Cell2 In Wb.Worksheets("Sheet2").Range("D2:D100") 
                   If Cell1.Text = Cell2.Text Then
                      Cell1.EntireRow.Interior.Color = ColorTranslator.ToOle(Color.Green)
                   End If
                Next Cell2
     Next Cell1

    i want to add the condition that on sheet2 column F : 

    For each text equal to "40 Cm" and Cell1.Text = Cell2.Text from first condition then color

    so it will color all rows on sheet1 that meets condition : text "40 Cm" and same Name in sheet2.

    Thanks by advance

    Tuesday, March 24, 2020 3:04 PM

All replies

  • Hi Ryutenkan,

    Thank you for posting here.

    According to your desciption, I make a test on my side, and here's the code you can refer to:

            Dim arr1 As Object(,) = New Object(6, 0) {}
            Dim arr2 As Object(,) = New Object(6, 0) {}
            Dim arr3 As Object(,) = New Object(6, 0) {}
            Dim wsht As Microsoft.Office.Interop.Excel._Worksheet = Wb.Sheets("Sheet1")
            Dim wsht2 As Microsoft.Office.Interop.Excel._Worksheet = Wb.Sheets("Sheet2")
            arr1 = wsht.Range("A1", "A7").Value2
            arr2 = wsht2.Range("A1", "A7").Value2
            arr3 = wsht.Range("C1", "C7").Value2
    
            For i As Integer = 1 To 7
                For j As Integer = 1 To 7
                    If arr1(i, 1).ToString() = arr2(j, 1).ToString() AndAlso arr3(i, 1).ToString() = "40Cm" Then
                        wsht.Range("A" & i, "A" & i).EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
                    End If
                Next
            Next

    My Sheet1 and Sheet2:

     

    Result of my test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 25, 2020 5:51 AM
    Moderator