• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

0
alsam
Asked:
alsam
  • 3
1 Solution
 
kgerbChief EngineerCommented:
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

0
 
kgerbChief EngineerCommented:
This code will color the appropriate row from columns A to AB
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
        Range(Cells(cll.Row, "A"), Cells(cll.Row, "AB")).Interior.ColorIndex = 3
        MsgBox cell
    End If
Next
End Sub

Open in new window

0
 
kgerbChief EngineerCommented:
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

0
 
Rob HensonIT & Database AssistantCommented:
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
0
 
alsamAuthor Commented:
Thanks...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now