VBA color entire row base on Cell text-CodePudding

example row B4 to AN4 to color green base on cell AF4 is ready as green , and in progress as yellow, and pending as red. this applied for all rows

CodePudding user response：

Untested but something along these lines should work:

dim rw as range
Set rw = Activesheet.Range("B4:AN4") 'start row

do while application.counta(rw)>0   'loop while there's data

    select case rw.entirerow.columns("AF").value
        case "ready": rw.interior.color = vbGreen
        case "in progress": rw.interior.color = vbYellow
        case "pending": rw.interior.color = vbRed
    end select         

    set rw = rw.offset(1, 0) 'next row 
loop

CodePudding user response：

Highlight specific columns of rows based on the given criteria

Sub ColourChange()

    Dim MySht As Worksheet
    Dim MyRng As Range
    Set MySht = ThisWorkbook.Sheets("Sheet1")
    Set MyRng = MySht.UsedRange.Columns("AF")
    
    For Each cell In MyRng.Cells
        Set activeRow = Range("B:AN").Rows(cell.Row)
        Select Case cell.Value    '// you can add the filter here
            Case "Available", "Resell"
                activeRow.Interior.Color = XlRgbColor.rgbLightGreen
            Case "Deal", "Sold  Excl", "Sold Excl", "Holdback", _
                 "Pending", "Expired", "Sold CoX"
                activeRow.Interior.Color = XlRgbColor.rgbRed
            Case "Sold nonX", "Sold NonX"
                activeRow.Interior.Color = XlRgbColor.rgbBlue
        End Select
    Next
End Sub