Button click find empty cell

Question

I am new to excel VBA and trying to do create a form to let user fill in.I got a problem of loop to find missing cell It is how the table look like The fields separate by 2 color red and green. Red is mandatory cells and Green is optional field. As you can see, there are some mandatory cells is missing by user. Therefore, I would like to create a button for user to check which one they should fill in after the checking. I would like to highlight the missing cell so they will know which one they fill in. It is the table should look like after clicking the check button

Now my problem is I used for each loop or for loops to find out the missing cells but i can only find those cells by setting the range like

dim rng As range 


Set rng = Range("B3:j10")

but It is a user fill in form It is a dynamic worksheet that I will never know how many data user will fill in. If i use my code, the worksheet will just non stop highlighting all the empty cells. I want the check validation can be detect and highlight the mandatory(Red fields) missing fill but not ALL the empty cells. Even a new user fill in the form the check button can still work properly

It is my code:

sub CommandButton1_click()

Dim rng As range 
Dim found, emptyCell

Set rng = Range("B3:J10")
emptyCell = IsEmpty(emptyCell)
found = false

For each emptyCell In rng
If emptyCell.Cells = "" Then
found = True
emptyCell.Interopr.ColorIndex = 6
Else 
emptyCell.Interopr.ColorIndex = 0
End If
End Sub

Answer 1

Try this: :)

Dim i As Byte
Dim rng As Range
Dim emptyCell As Range
For i = 3 To 10
  Set rng = Range(Cells(i, 2), Cells(i, 8))
  For Each emptyCell In rng
    If emptyCell = "" And Join(Application.WorksheetFunction.Index _
    (rng.Value, 1, 0)) <> "" And Cells(1, emptyCell.Column) _
    .Interior.ColorIndex = 3 Then
      emptyCell.Interior.ColorIndex = 6
      found = True
    Else
      emptyCell.Interior.ColorIndex = 0
    End If
  Next
Next

Change the For i = 3 to 10 for other rows

change the color [red = true] of your headers to check for other columns

change Set rng = Range(Cells(i, 2), Cells(i, 8)) to change the checked columns

Answer 2

This should do it.

The intersect function tests whether the two ranges cross. If they do not cross then it does not do anything.

Sub CommandButton1_click()

Dim rng As Range
Dim found As Boolean, emptyCell As Range
Dim lastrow As Long

lastrow = Range("B3").end(xldown).row

Set rng = Range("B3:J" & lastrow)
'emptyCell = IsEmpty(emptyCell)
found = False

For Each emptyCell In rng
If Not Intersect(emptyCell, Range("B4:B" & lastrow & ",D4:E" & lastrow & ",H4:I" & lastrow)) Is Nothing Then
    If emptyCell.value = "" Then
        found = True
        emptyCell.Interior.ColorIndex = 6
    Else
        emptyCell.Interior.ColorIndex = 0
    End If
End If
Next
End Sub