CODEWITHSUNDEEP
excelvba
Moonshine54
Moonshine54

Reputation: 25

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

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?

Upvotes: 1

Views: 39

Answers (1)

Lucas
Lucas

Reputation: 675

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

Upvotes: 1

Related Questions