CODEWITHSUNDEEP
vbaif-statementcellconditional-formatting
cheapkid1
cheapkid1

Reputation: 469

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

Upvotes: 0

Views: 24401

Answers (2)

lowak
lowak

Reputation: 1284

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

Upvotes: 2

Pane
Pane

Reputation: 555

Your conditions for StopRow aren't clear. Do you want to set StopRow when the cell has a conditional formatting rule or simply when it has a different format than the default ? A cell may have a rule but it may not be applied. Anyway, the function presented here is something you might find of use.

Copy the ActiveCondition function somewhere in a module and then change your for loop like so:

For i = 6 To StopRow
    If ActiveCondition(Cells(i,3))=0 Then StopRow=i : Exit For
    Cells(i, 3).Value = MasterValue
Next

If you want to check for change in font color that didn't come from conditional formatting then you'd need an extra line:

 For i = 6 To StopRow
        If ActiveCondition(Cells(i,3))=0 Then StopRow=i : Exit For
        If Cells(1,3).Font.ColorIndex=0 Then StopRow=i : Exit For 
        Cells(i, 3).Value = MasterValue
    Next

There are more elegant ways to do this but this solution is the easiest to implement in your code.

Hope this helps.

Upvotes: 1

Related Questions