Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
offsets - using ranges
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
offsets - using ranges
On Jan 24, 7:24 pm, Theo wrote:
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 . 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
offsets - using ranges
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 -- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
offsets - using ranges
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!! "Jim Cone" wrote: 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 -- 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offsets (I think) | Excel Discussion (Misc queries) | |||
Countif & Offsets | Excel Discussion (Misc queries) | |||
Named range using offsets | Excel Programming | |||
cutting and pasting ranges using offsets | Excel Programming | |||
average, array and offsets | Excel Worksheet Functions |