Find each row that has a Color (unknown color) and change the value of text in the cell

[HunterN]

Hi,

I have the following module that will go through the used range in the worksheet and find each row where the Interior.color is NOT equal to vbWhite and add the following Row name to the value.

Sub Colored_Cells()

    Dim LastRow As Long
    Dim myRow As Long
   
    Range("A1").Select
    
    myRow = ActiveCell.row
    
    'Find the last row in the range
    Call FindLastRow(passLast)
    LastRow = passLast
    
    For myRow = 1 To LastRow
      '  MsgBox ("this cells color is " & ActiveCell.Interior.color)
        
        If ActiveCell.Interior.color = vbWhite Then 
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Value = "Row number " & myRow
            ActiveCell.Offset(1, 0).Select
        End If
    Next
    
    Range("A1").Select
    
End Sub


Sub FindLastRow(passLast)
    
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by rows.
        LastRow = Cells.Find(What:="*", after:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).row
        passLast = LastRow
    End If

End Sub

I am just wondering if there is a better way to do this?

Thanks,
Nancy

 

[MrKowz]

It's best practice to avoid "selecting" cells in VBA code, unless your intention is to select a specific cell. Here, we can directly refer to cells without selecting. Try:

Sub Colored_Cells()

    Dim LastRow As Long
    Dim myRow As Long
   
    'Find the last row in the range

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    For myRow = 1 To LastRow
        With Range("A" & myRow)
            If .Interior.Color <> vbWhite Then
                .Value = "Row number " & myRow
            End If
        End With
    Next myRow
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[HunterN]

It's best practice to avoid "selecting" cells in VBA code, unless your intention is to select a specific cell. Here, we can directly refer to cells without selecting. Try:

Sub Colored_Cells()

    Dim LastRow As Long
    Dim myRow As Long
   
    'Find the last row in the range

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    For myRow = 1 To LastRow
        With Range("A" & myRow)
            If .Interior.Color <> vbWhite Then
                .Value = "Row number " & myRow
            End If
        End With
    Next myRow
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

Thanks so much for giving me a cleaner way to do this (without Selecting).

Do I have to do the Application.Calculation part. What purpose does this serve?

Thanks,
Nancy

 

[MrKowz]

The application.calculation part(s) turn on and off calculations in the Excel application. I recommend using this any time you are adjusting the values of cells, as it is never known (from my perspective) if the cells we are changing are inputs to formulas. If they are inputs to formulas, and we do not turn off the calculations, then Excel will want to recalculate every single time we change one of these "input" cells. When we turn the calculation mode to xlCalculationManual, this tells Excel to not calculate unless we explicitly tell it to... this can greatly speed up code execution!

Hope that helps

 

[HunterN]

The application.calculation part(s) turn on and off calculations in the Excel application. I recommend using this any time you are adjusting the values of cells, as it is never known (from my perspective) if the cells we are changing are inputs to formulas. If they are inputs to formulas, and we do not turn off the calculations, then Excel will want to recalculate every single time we change one of these "input" cells. When we turn the calculation mode to xlCalculationManual, this tells Excel to not calculate unless we explicitly tell it to... this can greatly speed up code execution!

Hope that helps

Thanks for that explanation. I need to remember that.

Thanks again for your help, it was exactly what I needed.

Nancy

 

[MrKowz]

Thanks for that explanation. I need to remember that.

Thanks again for your help, it was exactly what I needed.

Nancy

Wonderful - thanks for the feedback. Have a good day!