Excel Macro Find and fill Blanks Help!@

[xxcucumberxx]

I cant get my Macro to find and fill blanks in column "m" starting from row 2. It fills the blanks but it also fill the blanks all the way down to the end of the spreadsheet. I just need it to fill to the last row of data. Also to reset the color when i rerun the macro. So i can fix it and rerun it and so it un-colors the ones with data in it


Columns("M:M").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Range("O6").Select


Dim myRange As Range
Set myRange = Sheet1.Range("M2:M5000")


'clear all color
myRange.Interior.ColorIndex = xlNone


'color only blank cells
myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6




Please help me fix this. Thank you so much who does. Maybe there is a different formula i could use or something

 

[Joe4]

Try this:

Sub MyColor()

    Dim myLastRow As Long
    Dim myRange As Range
    
'   Find last row in column M with data
    myLastRow = Cells(Rows.Count, "M").End(xlUp).Row
    
'   Set range to apply to
    Set myRange = Sheet1.Range("M2:M" & myLastRow)

'   Remove font color from all cells in row M
    With myRange.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

'   Clear all fill color
    myRange.Interior.ColorIndex = xlNone

'   Color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

End Sub

 

[igold]

Hi,

Try this:

Sub FillBlanks()


    Dim myRange As Range
    Columns("M:M").Interior.ColorIndex = xlAutomatic
    Set myRange = Sheet1.Range("M2:M" & Cells(Rows.Count, 13).End(xlUp).Row)


    'clear all color
    myRange.Interior.ColorIndex = xlNone


    'color only blank cells
    On Error Resume Next
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6


End Sub

Joe, if there are no blank cells your code will halt.

igold

 

[Joe4]

Joe, if there are no blank cells your code will halt.

Its at the very end of the code, and there is nothing left to do at that point, so that error message can be easily rectified, i.e.

Sub MyColor()

    Dim myLastRow As Long
    Dim myRange As Range
    
'   Find last row in column M with data
    myLastRow = Cells(Rows.Count, "M").End(xlUp).Row
    
'   Set range to apply to
    Set myRange = Sheet1.Range("M2:M" & myLastRow)

'   Remove font color from all cells in row M
    With myRange.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

'   Clear all fill color
    myRange.Interior.ColorIndex = xlNone


'   Color only blank cells
[COLOR=#ff0000]    On Error Resume Next[/COLOR]
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
[COLOR=#ff0000]    On Error GoTo 0[/COLOR]

End Sub

Its also usually good form to add that second line:
On Error GoTo 0
in case more code gets added below it in the future, it doesn't end up ignoring other errors without you realizing it.

 

[igold]

Joe,

Good point on the On Error Goto 0. Thank you.

Regards,

igold

 

[Joe4]

Good point on the On Error Goto 0. Thank you.

No problem. Good catch on the error possibility too. It doesn't affect the outcome, but of course no one wants to see error messages!