Get color in the first cell and remove all other cells color

  • Hi friends,


    I don't know the exact subject line.


    With another vba, I am getting color in Column A based on another cell value, But I am getting color in multiple cells with the same number. I want to get color in the first cell (based on cell value) and remove all other cells color.
    Please see attachment for better understanding.


    Thank you.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you Mumps,


    Well, I don't want every first number will be colored, I want, those first numbers, which the same second or third or etc. numbers were already colored. Please check my Post 1 attachment. My need mentioned from E2.


    Thank you.

  • Finally I got a code as below :


    [vba]


    Sub Remove_Color3()


    Dim i As Integer


    Application.ScreenUpdating = False
    For i = 2 To 255


    If Cells(i, 1) = Cells(i + 1, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 1, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 1, 1).Font.Bold Then
    Cells(i + 1, 1).Interior.ColorIndex = iNone
    Cells(i + 1, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 2, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 2, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 2, 1).Font.Bold Then
    Cells(i + 2, 1).Interior.ColorIndex = iNone
    Cells(i + 2, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 3, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 3, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 3, 1).Font.Bold Then
    Cells(i + 3, 1).Interior.ColorIndex = iNone
    Cells(i + 3, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 4, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 4, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 4, 1).Font.Bold Then
    Cells(i + 4, 1).Interior.ColorIndex = iNone
    Cells(i + 4, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 5, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 5, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 5, 1).Font.Bold Then
    Cells(i + 5, 1).Interior.ColorIndex = iNone
    Cells(i + 5, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 6, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 6, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 6, 1).Font.Bold Then
    Cells(i + 6, 1).Interior.ColorIndex = iNone
    Cells(i + 6, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 7, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 7, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 7, 1).Font.Bold Then
    Cells(i + 7, 1).Interior.ColorIndex = iNone
    Cells(i + 7, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 8, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 8, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 8, 1).Font.Bold Then
    Cells(i + 8, 1).Interior.ColorIndex = iNone
    Cells(i + 8, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 9, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 9, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 9, 1).Font.Bold Then
    Cells(i + 9, 1).Interior.ColorIndex = iNone
    Cells(i + 9, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 10, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 10, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 10, 1).Font.Bold Then
    Cells(i + 10, 1).Interior.ColorIndex = iNone
    Cells(i + 10, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 11, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 11, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 11, 1).Font.Bold Then
    Cells(i + 11, 1).Interior.ColorIndex = iNone
    Cells(i + 11, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 12, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 12, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 12, 1).Font.Bold Then
    Cells(i + 12, 1).Interior.ColorIndex = iNone
    Cells(i + 12, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 13, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 13, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 13, 1).Font.Bold Then
    Cells(i + 13, 1).Interior.ColorIndex = iNone
    Cells(i + 13, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 14, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 14, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 14, 1).Font.Bold Then
    Cells(i + 14, 1).Interior.ColorIndex = iNone
    Cells(i + 14, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 15, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 15, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 15, 1).Font.Bold Then
    Cells(i + 15, 1).Interior.ColorIndex = iNone
    Cells(i + 15, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 16, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 16, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 16, 1).Font.Bold Then
    Cells(i + 16, 1).Interior.ColorIndex = iNone
    Cells(i + 16, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 17, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 17, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 17, 1).Font.Bold Then
    Cells(i + 17, 1).Interior.ColorIndex = iNone
    Cells(i + 17, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 18, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 18, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 18, 1).Font.Bold Then
    Cells(i + 18, 1).Interior.ColorIndex = iNone
    Cells(i + 18, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 19, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 19, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 19, 1).Font.Bold Then
    Cells(i + 19, 1).Interior.ColorIndex = iNone
    Cells(i + 19, 1).Font.Bold = False
    End If


    If Cells(i, 1) = Cells(i + 20, 1) And _
    Cells(i, 1).Interior.ColorIndex = Cells(i + 20, 1).Interior.ColorIndex And _
    Cells(i, 1).Font.Bold = Cells(i + 20, 1).Font.Bold Then
    Cells(i + 20, 1).Interior.ColorIndex = iNone
    Cells(i + 20, 1).Font.Bold = False
    End If




    Next i
    Application.ScreenUpdating = True


    End Sub
    [\vba]





    will anyone please try to reduce the above code please.


    Thank you.

  • In first post you say...


    Quote

    I want to get color in the first cell (based on cell value) and remove all other cells color.


    then


    Thank you Mumps,


    Well, I don't want every first number will be colored, I want, those first numbers, which the same second or third or etc. numbers were already colored. Please check my Post 1 attachment. My need mentioned from E2.


    Thank you.


    which one is correct?


    You ignore my message where I mention that you can do this without VBA code.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!