Vba if else statement

QuestionEdit

Mr. Ogilvy,

I see a lot of answers from you so I would like to ask you a question as you seem very knowledgeable in this field.

I am trying to make a simple if else statement in VBA. I just want to have predetermined cells test and if the cell above it is hidden then have it display "H1" and if the cell above it is not hidden then have it display "H2". Currently it is displaying H1 in these cells whether the ones above them are hidden or not. There are no errors or anything that pop up when run. My code is as follows:

With Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")

      .Select

          For Each Cell In Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")
              If Cells.Offset(-1, 0).Hidden = True Then
                  Cell.Value = "H1"
              ElseIf Cells.Offset(-1, 0).Hidden = False Then
                  Cell.Value = "H2"
              End If
          
          Next Cell

AnswerEdit

Jeff,

It appears you have a typo in your code. You use Cells instead of Cell. Also, the hidden property is a property of the entirerow or entirecolumn

With Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")

     .Select

         For Each Cell In Range("B5, B9, B13, B17, B21, B25, B29, B33, B37, B41, B45, B49, B53, B57, B61, B65, B69, B73, B77, B81, B85, B89, B93, B97, B101")
             If Cell.Offset(-1, 0).EntireRow.Hidden = True Then
                 Cell.Value = "H1"
             ElseIf Cell.Offset(-1, 0).EntireRow.Hidden = False Then
                 Cell.Value = "H2"
             End If
         
         Next Cell

Another way you might do this

dim i as Long for i = 4 to 100 step 4

 if cells(i,"B").EntireRow.Hidden = True then
    cells(i + 1, "B").Value = "H1"
 else
    cells(i + 1, "B").Value = "H2"
 end if

next i