Code for command button to unfill cell by cell

[nayrbnalhguoc]

I have a data set with columns and headers as normal. I want to reveal the data cell by cell using a command button so every time I click the button it reveals the data in each cell e.g b2, b3, b4 ,b5 etc and then moves on to C2, C3 C4 and so on. Does anyone have code to do that so it tabs automatically the whole way down the table without having to click the cells individually? Not sure if the below pic is a VBA interface or excel sheet using command button. Looking for something along these lines to reveal scores at a sports event.

 

[Z51]

The following code (linked to a command button) finds the last used row in column A and uses that as the last row to scan in each column. It finds the last used column in row 1, too. It then loops through each row of each column (starting in column 2, row 2) and checks if the cell fill (interior) color is black (1). If so, it sets it to no fill (0) and then exits the macro.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long, lastrow As Long, lastcol As Long, arr() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To lastcol
    For j = 2 To lastrow
        If Cells(j, i).Interior.ColorIndex = 1 Then
            Cells(j, i).Interior.ColorIndex = 0
            Exit Sub
        End If
    Next j
Next i
End Sub

 

[nayrbnalhguoc]

The following code (linked to a command button) finds the last used row in column A and uses that as the last row to scan in each column. It finds the last used column in row 1, too. It then loops through each row of each column (starting in column 2, row 2) and checks if the cell fill (interior) color is black (1). If so, it sets it to no fill (0) and then exits the macro.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long, lastrow As Long, lastcol As Long, arr() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To lastcol
    For j = 2 To lastrow
        If Cells(j, i).Interior.ColorIndex = 1 Then
            Cells(j, i).Interior.ColorIndex = 0
            Exit Sub
        End If
    Next j
Next
[/QUOTE]

The following code (linked to a command button) finds the last used row in column A and uses that as the last row to scan in each column. It finds the last used column in row 1, too. It then loops through each row of each column (starting in column 2, row 2) and checks if the cell fill (interior) color is black (1). If so, it sets it to no fill (0) and then exits the macro.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long, lastrow As Long, lastcol As Long, arr() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To lastcol
    For j = 2 To lastrow
        If Cells(j, i).Interior.ColorIndex = 1 Then
            Cells(j, i).Interior.ColorIndex = 0
            Exit Sub
        End If
    Next j
Next i
End Sub

Thanks so much for this @Z51
This starts at row 2, col B and works down B3, B4 etc as required.

Is it possible to reveal columns A & B row 2 (2 Cells simultaneously) to start with all the way down and then move to col C row 2 (1 cell)? How do I amend the above code to do that.

 

[Z51]

Added a check to see if the current column number (i) is 2 (column B), and if so, change the background color of the adjacent cell in column A as well.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long, lastrow As Long, lastcol As Long, arr() As Variant
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To lastcol
    For j = 2 To lastrow
        If Cells(j, i).Interior.ColorIndex = 1 Then
            If i = 2 Then Cells(j, i - 1).Interior.ColorIndex = 0
            Cells(j, i).Interior.ColorIndex = 0
            Exit Sub
        End If
    Next j
Next i
End Sub

 

[nayrbnalhguoc]

Thank you so much - that is exactly what I am looking for for this table!!
Could I be cheeky then to ask one more question:
I am also looking to develop another worksheet with a similar table all in black but i want to reveal each row in full (by unfilling the black colour). Is it possible to send on code to allow the button to unfill each row of black in full starting at row 2?

 

[Z51]

Private Sub CommandButton1_Click()
Dim i As Long, lastcol As Long
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, 1).Interior.ColorIndex = 1 Then
        Range(Cells(i, 1), Cells(i, lastcol)).Interior.ColorIndex = 0
        Exit Sub
    End If
Next i
End Sub

 

[nayrbnalhguoc]

Private Sub CommandButton1_Click()
Dim i As Long, lastcol As Long
lastcol = Range("XFD1").End(xlToLeft).Column

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, 1).Interior.ColorIndex = 1 Then
        Range(Cells(i, 1), Cells(i, lastcol)).Interior.ColorIndex = 0
        Exit Sub
    End If
Next i
End Sub

Thank you for all the help. Much appreciated