asked on

How to check if cell is empty in column and change colour for the row

Hi,
I'm trying to check if cell is empty or not in particular range (in column A)...Ok , i now how to check it ,but I don't know how to make the following:
If cell is not empty in defined range then I want to select that particular row (wher not empty cell is found) and change the row colour...Here is exacly what I want to make:

If not empty cell is found in range A10 then I want to select 10th row in range A10:AB10 and change color for selected cells...

Please help me to solve this..

Code for checking if cell is empty is belowe...

Regards.

Dim r As Range
    Set r = ThisWorkbook.ActiveSheet.Range("A10:A1500")
    For Each cell In r
        If IsEmpty(cell) Then
            Else
            MsgBox cell
        End If
    Next

Open in new window

kgerb

Couldn't tell if you wanted the entire row colored or just columns A and B. If you want entire row colored reverse commented lines.

Kyle

Sub colorrow()
Dim r As Range, cll As Range
Set r = ThisWorkbook.ActiveSheet.Range("A10:A1500")
For Each cll In r
    If Not IsEmpty(cell) Then
        cll.Resize(, 2).Interior.ColorIndex = 3
        'cll.EntireRow.Interior.ColorIndex = 3
        MsgBox cell
    End If
Next
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kgerb

Sorry for the spam :), forgot to change the variables from cell to cll in a few places

Sub colorrow()
Dim r As Range, cll As Range
Set r = ThisWorkbook.ActiveSheet.Range("A10:A1500")
For Each cll In r
    If Not IsEmpty(cll) Then
        Range(Cells(cll.Row, "A"), Cells(cll.Row, "AB")).Interior.ColorIndex = 3
        MsgBox cll
    End If
Next
End Sub

Open in new window

Rob Henson

Wouldn't actually need code to do this. You could do it with conditional formatting.

Select the area you need formatting, select the Conditional Formatting window and depending on which version of Excel choose the Formula option. Assuming A1 is active cell, enter the following:

=ISBLANK($A1)

Then choose the formatting.

Thanks
Rob H

alsam

ASKER

Thanks...