Easy way to tell if a range of cells in Excel has anything in it

[cycledick]

I have 5 groups of 10 cells in an Excel worksheet. I want to do one thing if ALL 10 CELLS in one group are empty, or another thing if 1 or more of the cells of that same group are populated. So, lets say if all 10 cells are empty, I want to change the fill color of a rectangle (drawing) to white But, if any of those 10 cells has a value in it, then I want to change the fill color to "no fill". Make sense?

I've figured out a way of doing it by checking each cell and adding to an integer. But, it ends up being a lot of code since I have to do it 5 times.

Here's a quick sample of what I THOUGHT would work...

Sub Test
If Worksheets("WorksheetWithMyCells").Range("H16:H42").Value = "" Then (blah blah blah) ELSE (blah blah blah)
End If
End Sub

Thats the same code I used to check one cell, but when I put a range of cells it gives me a "Type Mismatch" error. Is there an easy way to do that without running a loop?

Thanks!

 

[SkipVought]

Hi,

How about using the COUNTA spreadsheet function...

dim iCnt as integer
iCnt = application.counta(Worksheets("WorksheetWithMyCells").Range("H16:H42"))
select case iCnt
  case 0
    'set fill to white

  case else
    'set fill to none

end select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cycledick]

No, but you led me to 'countblank'. Just what I was looking for. The cells I'm checking have text in them, not just numbers, but the countblank worked perfectly and is much faster! Thanks Skip!

 

[SkipVought]

"No, but you led me to 'countblank'"

Excuse me, but COUNTA counts cells with ANY value or formula, including TEXT. COUNTBLANK is the complement of COUNTA, counting cells with no value or formula, including text.

Six of one; half a dozen of the other!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cycledick]

I wasn't inferring counta wouldn't work, just that countblank was exactly what I needed. I just wanted to check to see if they were all blank or not, I didn't really care about anything else. Countblank worked perfectly and took me down to about a third of the number of lines of code. I had been looking on the internet and in the help file for hours and I couldn't wrap my head around anything else other than checking each cell individually. I wouldn't have found countblank if you hadn't responded. As far as I'm concerned counta/countblank same thing, you get 100% of the credit.