Looping through rows, if 3 cells meet the criteria then highlight cells, if not then hide row

1
votes

I would like to set up a spreadsheet that, when you click a button, looks the cells in column 4, 7, and 8 in each row and if the criteria of the three cells is correct, the entire row highlights. If the criteria is not correct I want to hide the incorrect rows.

I seem to be getting stuck where the code selects the row to highlight. I had tried using ActiveCell.EntireRow.Interior.Color but it only ever either highlights everything in the range or highlights the row of whatever cell was selected before clicking the button.

Private Sub CommandButton1_Click()
Dim rng As Range: Set rng = Application.Range("A5:I54")
Dim cell As Range
Dim row As Range
Dim i As Integer

       For i = 1 To rng.Rows.Count
                If rng.Cells(RowIndex:=i, ColumnIndex:=4).Text = "Yes" And rng.Cells(RowIndex:=i, ColumnIndex:=7).Text = "Yes" And rng.Cells(RowIndex:=i, ColumnIndex:=8).Text = "No" Then
                rng.EntireRow.Interior.Color = vbYellow
                ElseIf rng.Interior.ColorIndex = vbWhite Then rng.EntireRow.Hidden = True
                End If
        Next

End Sub

How can I get the code to recognise the criteria of the row and highlight it correctly?

1
excelvba
Your problem is that you are setting the column index for the standard column numbers, but you are working within a specified rng, so the first column of the range would be 1 not 4. – GMalc

1 Answers

1
votes

If I understood your problem, this code will do it:

Private Sub CommandButton1_Click()

For i = 5 To 54
    If Cells(i, 4) = "Yes" And Cells(i, 7) = "Yes" And Cells(i, 8) = "No" Then
        Range(Cells(i, 4), Cells(i, 9)).Interior.Color = vbYellow
    ElseIf Cells(i, 4).Interior.Color = vbWhite Then
        Cells(i, 4).EntireRow.Hidden = True
    End If
Next

End Sub