checking a range for at least one
 

I have code that will hi-light a cell if it is empty based on input in the
first cell:

For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then
If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone
End If

Now I still need to do:
For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then

but now I need to check to see if a range of cells are all null (at least
one of them
must NOT be null)

For example - if R:V and X are empty, then I want to hi-light R:V and X to
show that at least one of them must have data.

Any help is MUCH appreciated.

checking a range for at least one
 

First, read this from VBA help.

IsEmpty only returns meaningful information for variants.

This suggestion is based on what you described.
myRange = Range(ToBeDefined)
If cll.Offset(0, 1) = "" Then
cll.Offset(0, 1).Interior.ColorIndex = 6
For Each c In myRange 'myRange must be defined
If Not c Is Nothing Then
MsgBox "Data Here " & c.Address
End If
Next
End If
"Theo" wrote:

I have code that will hi-light a cell if it is empty based on input in the
first cell:

For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then
If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone
End If

Now I still need to do:
For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then

but now I need to check to see if a range of cells are all null (at least
one of them
must NOT be null)

For example - if R:V and X are empty, then I want to hi-light R:V and X to
show that at least one of them must have data.

Any help is MUCH appreciated.

checking a range for at least one
 

You may find applying Format|conditional formatting easier (and quicker to
implement).

But...

dim cll as range
with ActiveSheet
for each cll in .range("a:a").cells
if lcase(cll.value) = "a" then
if isempty(cll.offset(0,1).value) then
cll.offset(0,1).interior.colorindex = 6
else
cll.offset(0,1).interior.colorindex = xlnone
end if

if application.counta(.cells(cll.row,"R").resize(1,5) ) = 0 _
and isempty(.cells(cll.row,"X").value) then
'all cells empty
'do your formatting
.cells(cll.row,"R").resize(1,5)).interior.colorind ex = 6
.cells(cll.row,"X").interior.colorindex = 6
else
.cells(cll.row,"r").resize(1,5).interiorcolorindex = xlnone
.cells(cll.row,"X").interior.colorindex = xlnone
end if
end if
next cll
end with

(untested, uncompiled. watch for typos)

You may want to limit yourself to the range to check.

Maybe...
with ActiveSheet
for each cll in .range("a1", .cells(.rows.count,"A").end(xlup)).cells
....


Theo wrote:

I have code that will hi-light a cell if it is empty based on input in the
first cell:

For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then
If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone
End If

Now I still need to do:
For Each cll In Range("A:A")
If cll.Value = "A" Or cll.Value = "a" Then

but now I need to check to see if a range of cells are all null (at least
one of them
must NOT be null)

For example - if R:V and X are empty, then I want to hi-light R:V and X to
show that at least one of them must have data.

Any help is MUCH appreciated.

--

Dave Peterson