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 . |
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 |
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 . |
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 . |
All times are GMT +1. The time now is 08:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com