offsets - using ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Theo - 25 Jan 2008 00:24 GMT

This works fine:
If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone

I want to do the same thing but for a range of cells in the row (from colum
R to column V, for example - check to see if they are ALL empty and then
highlight ALL of them, if so).
Any help is much appreciated .

Reply to this Message

SteveM - 25 Jan 2008 00:47 GMT

> This works fine:
> If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
[quoted text clipped - 4 lines]
> highlight ALL of them, if so).
> Any help is much appreciated .

If you want to do a by-cell check, this will work

Sub CheckCells()
Dim cell as Range

For Each cell in Range("R#:V#") 'whatever the row numbers are]
If IsEmpty(cell)... Then
your code here
End If
Next

End Sub

Generally it's good practice to set the range to a range variable and
use that downstream.

Good Luck,

SteveM

Reply to this Message

Jim Cone - 25 Jan 2008 01:22 GMT

Or another way...

Sub DoSomething()
If Application.CountA(Range("B5:F5")) = 0 Then
Range("B5:F5").Interior.ColorIndex = 15
Else
Range("B5:F5").Interior.ColorIndex = xlColorIndexNone
End If
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Theo"
wrote in message
This works fine:
If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone

I want to do the same thing but for a range of cells in the row (from colum
R to column V, for example - check to see if they are ALL empty and then
highlight ALL of them, if so).
Any help is much appreciated .

Reply to this Message

Theo - 25 Jan 2008 17:40 GMT

I tried to add that, but got unexpected results (it high-lighted the entire
column, not just the row). I am trying to insert that logic here ******

Sub HiLite()
For Each cll In Range("A:A")
If cll.Value = "C" Or cll.Value = "c" Then

If IsEmpty(cll.Offset(0, 1)) Then cll.Offset(0, 1).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 1).Interior.ColorIndex = xlNone
If IsEmpty(cll.Offset(0, 2)) Then cll.Offset(0, 2).Interior.ColorIndex = 6 _
Else: cll.Offset(0, 2).Interior.ColorIndex = xlNone

***** If cells R - U are ALL empty, then high-light. R - U; if ONE of them
is NOT empty then NONE of the cells are high-lighted *********
End If
Next
End Sub

Thanks for any help!!

> Or another way...
>
[quoted text clipped - 5 lines]
> End If
> End Sub

Reply to this Message