vb/excel question with ranges

[kleinma]

I have a spreadsheet and was asked to write a VB Macro that will scan the spreadsheet and change the BG color of any cells that are not filled.. IE have a vakue of ""

so i can do this easy enough i think... but i need to find the limits of the sheet.. so i figure i have to scan down and scan across to find the ends of the data.. for example the sheet looks like this

212.44.42.827       done    done           done
741.171.456.747     done    done    done   done
123.14.4.487                done    done
173.14.46.681       done    done    done   done
113.142.46.888      done                   done
583.44.46.833       done    done    done

so only where "done" is missing i want to change the BG color... any help on this??? I don't do much excel automation.. i am not so familiar with excels methods and properties

[alex_read]

Sorted, that one didn't take too much code - not what I was expecting! btw, I'm not sure if this'll cover all cell formats, so you might wanna put another nested if statement in there checking the cell value using the isnull() function too incase that code doesn't fully cover your spreadsheet!

Private Sub CommandButton1_Click()
    For Each Cell In Range("A1:" & Cells.SpecialCells(xlCellTypeLastCell).Address)
        If CStr(Trim(Cell.Value)) = "" Then
            Cell.Interior.Color = &HC0FFFF
        End If
    Next Cell
End Sub

[Frans C]

If you use Excel2000 or later, you can do this with no code at all, using conditional formatting.
Select the entire sheet (or the range you want to cover), and select Format --> Conditional formatting from the menu.
Make a condition like:
Cell value is
equal to
= ""

and click the Format button to set your format.

This way the sheet is updated as soon as a change is made. You don't have to run a macro anymore.

[kleinma]

thanks both of u

[kleinma]

Originally posted by Frans C
If you use Excel2000 or later, you can do this with no code at all, using conditional formatting.
Select the entire sheet (or the range you want to cover), and select Format --> Conditional formatting from the menu.
Make a condition like:
Cell value is
equal to
= ""

and click the Format button to set your format.

This way the sheet is updated as soon as a change is made. You don't have to run a macro anymore.

the only reason i don't like this approach is that it will make the entire sheet the color i pick.. when i say entire sheet i mean the cells past where the fields end..

[agmorgan]

Another property that doesn't seem to get mentioned much is

UsedRange

This would also give you a way of selecting the correct cells.

For Each Cell In ActiveSheet.UsedRange.Select
        If CStr(Trim(Cell.Value)) = "" Then
            Cell.Interior.Color = &HC0FFFF
        End If
    Next Cell

Its also easier on the eye.
But each to his own.

/Andrew

[ITboy]

Hi I've used the code aboove to embed in my own app
but
for this" For Each Cell " It highllight "Cell"and says "Variable not defined" What to do?y?

[ITboy]

Its ok... I've declared Cell as Range and it works..