1

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
pnuts
  • 58,317
  • 11
  • 87
  • 139
Nicholas Agger Lui
  • 569
  • 1
  • 4
  • 13

2 Answers2

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Can i not do Set rng = Range("B3:J10") for this problem ? I don't want to limited the range because I don't know how many people will fill this in – Nicholas Agger Lui Oct 29 '15 at 16:50
  • @NicholasAggerLui I have made a change so you can set the last row. Using the full range will not work as you will color blanks that are not needed to color. To dynamically find the last row see [this](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). – Scott Craner Oct 29 '15 at 17:02
  • @NicholasAggerLui I just fixed some typos in the code. Please use this one. – Scott Craner Oct 29 '15 at 17:11
  • Thx for your help, I have tested the code and found out that even B7 cell and below are also highlighted. For example, there are only 3 people who fill in the form so there should be D7 E6,H4 ,I5 are highlighted but maybe next day few more people will also fill in the form i would like to omit the highlight in "unused " row – Nicholas Agger Lui Oct 29 '15 at 17:19
  • I am not sure if offset() can do it? – Nicholas Agger Lui Oct 29 '15 at 17:26
  • @NicholasAggerLui Okay I misread the question, I have made an edit. – Scott Craner Oct 29 '15 at 17:26
  • Thank you very much ! i should read more about those xl(poistion) – Nicholas Agger Lui Oct 29 '15 at 17:31
0

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

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31