Search code examples
vbaif-statementcellconditional-formatting

vba how to check if a cell has no formatting or color

I recently had a question regarding how to copy a cell value into all cells below it and stop based on when column A got to a blank cell. post

The excel sheet I'm working in has many divider rows that are color filled across the entire row to separate categories as you scroll down the sheet. I would like to be able to skip these separating rows in column A when the macro checks for a blank cell in column A. Or I would like to just assign StopRow to the first cell which has no formatting/no color/no value.

Here is what I have, thanks to Ripster earlier today, but I've failed incorporating a proper if then statement with what he came up with.

 Sub Example()
    Dim MasterValue As String
    Dim StopRow As Long
    Dim i As Long

    'Get the master value
    MasterValue = Range("C5").Value

    'Get the first blank cell in column A
    StopRow = Range("A1").End(xlDown).Row

    'Start at row 6 and continue to the "Stop Row"
    For i = 6 To StopRow
        'Set every cell from row 6 in column 3 to the "Master Value"
        Cells(i, 3).Value = MasterValue
    Next
End Sub

Please help. IE

Thanks

Solution

  • This took me a while but I found solution to your problem ;)

    If macro goes to cell with different color - checking and do nothin, next "i" is taken. This should do what u want. It's possible to add more color ;)

    Link to colors - http://dmcritchie.mvps.org/excel/colors.htm

    Sub Example()

For i = 6 To 1200
    If Cells(i, 3).Interior.ColorIndex = 1 Then 'here is color black

    Else
        If IsEmpty(Cells(i, 1)) = True Then 'if cells in column "A" is empty then stop
            Exit For

        Else
        Cells(i, 3).Value = Range("C5").Value
        End If

   End If

Next i

    End Sub