formula/macro to colour blank cell in a range

**EAMOG** · 06-29-2010, 03:43 PM

HI All,

i have macro which pulls information from another excel sheet.

what i am looking to do is check a column for blanks and highlight

i was going to add in the following into my script but having some problems

Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).Formula = "=IF(C2="""" Then Selection.Interior.ColorIndex = vbBlack)"

am i far off the mark with this

..fairly new to VBA, any help appreciated

thank you

**snb** · 06-29-2010, 04:06 PM

sheets(1).columns(3).specialcells(4).interior.colorindex=4

**Marcol** · 06-29-2010, 04:17 PM

Try this

Sub Test()
    Dim rng As Range
    Dim cell As Object
    
    Set rng = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    For Each cell In rng
        If cell = "" Then
            cell.Interior.ColorIndex = 1
        End If
    Next
End Sub

But some how I suspect that conditional formatting is what you will eventually need

Hope this helps