none
VBA code to color row if its within a percentage range RRS feed

  • Question

  • Hello VBA experts! This question seems easy but I have challenges completing it.
    Basically what its doing is to color code a cell if its within these ranges. the problem is its coloring the entire row. I only need the cell in Col N to turn green, red or yellow when conditions are met. 

    Any help is appreciated!

    Thanks a lot!

    0-50% - red
    50-75- yellow
    75-up- green

    'Color code

    Dim myRow As Long

    Dim lastrow As Long    ' *** last row with a filled cell in column A

     

      With ActiveSheet

        lastrow = Range("N" & Rows.count).End(xlUp).row

        ' --- column N=14

        For myRow = 2 To lastrow   ' *** using lastRow

          If (.Cells(myRow, 14).Value <= 0.51) Then

            'And (.Cells(myRow, 25).Value = "") Then

              .Rows(myRow).Interior.ColorIndex = 3

          Else

              .Rows(myRow).Interior.ColorIndex = xlNone

          End If

        Next myRow

      End With

    Wednesday, August 9, 2017 11:15 PM

Answers

  • Hi IamJackie,

    from the description of the thread, I understand that you want to match the value in column 'N' and want to set background color of cell in column 'N'.

    please refer example below.

    Sub color_demo()
    Dim lastrow As Long
    lastrow = Range("N" & Rows.Count).End(xlUp).Row
    Dim iCntr
    For iCntr = 2 To lastrow
    If (Cells(iCntr, 14).Value <= 50) Then
    Cells(iCntr, 14).Interior.ColorIndex = 3
    ElseIf (Cells(iCntr, 14).Value > 50 And Cells(iCntr, 14).Value <= 75) Then
    Cells(iCntr, 14).Interior.ColorIndex = 6
    Else
    Cells(iCntr, 14).Interior.ColorIndex = 4
    
    End If
    Next iCntr
    End Sub

    Output:

    this is just an example, you can try to modify the code as per your requirement.

    Regards

    Deepak


    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.


    Thursday, August 10, 2017 8:23 AM
    Moderator

All replies

  • Use ElseIf like:

    If (.Cells(myRow, 14).Value <= 0.50) Then

                .Rows(myRow).Interior.ColorIndex = 3

    ElseIf (.Cells(myRow, 14).Value >= 0.51) AND (.Cells(myRow, 14).Value <= 0.70)) Then

             .Rows(myRow).Interior.ColorIndex = 2

    Else

              .Rows(myRow).Interior.ColorIndex = xlNone

    End If

    Note: The code just sample not syntax error free.

    chanmm


    chanmm

    Wednesday, August 9, 2017 11:27 PM
  • Hi IamJackie,

    from the description of the thread, I understand that you want to match the value in column 'N' and want to set background color of cell in column 'N'.

    please refer example below.

    Sub color_demo()
    Dim lastrow As Long
    lastrow = Range("N" & Rows.Count).End(xlUp).Row
    Dim iCntr
    For iCntr = 2 To lastrow
    If (Cells(iCntr, 14).Value <= 50) Then
    Cells(iCntr, 14).Interior.ColorIndex = 3
    ElseIf (Cells(iCntr, 14).Value > 50 And Cells(iCntr, 14).Value <= 75) Then
    Cells(iCntr, 14).Interior.ColorIndex = 6
    Else
    Cells(iCntr, 14).Interior.ColorIndex = 4
    
    End If
    Next iCntr
    End Sub

    Output:

    this is just an example, you can try to modify the code as per your requirement.

    Regards

    Deepak


    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.


    Thursday, August 10, 2017 8:23 AM
    Moderator
  • 0-50% - red
    50-75- yellow
    75-up- green

    Why not use a conditional formatting?

    Andreas.

    Thursday, August 10, 2017 2:11 PM
  • Thank you so much! This worked!
    Thursday, August 10, 2017 2:44 PM
  • Hello Andreas, 

    I didnt want to use conditional formatting since it will affect the size and speed of my spreadsheet. I say that based from experience. :) 

    Thank you! 

    Thursday, August 10, 2017 2:44 PM