Need VBA for IF cell filled AND other cell blank then throw ERROR pop up

[TresRing]

I looked through past questions but did not find a question or solution that completely matched what I need.

I need a macro that will throw an error IF any cell in column B is populated AND any other cell in that row is blank. It can't be coded into the cell directly because the data will be entered (pasted) into the worksheet from a different place and the formula would be overwritten. The worksheet will be around 4000 lines, so too error-prone and time consuming to manually scan for missing data.

For example, in this table each row has one blank cell.

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]ID
[/TD]
[TD]Activity
[/TD]
[TD]Date
[/TD]
[TD]Variance
[/TD]
[TD]Metric
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B4590F
[/TD]
[TD]2/1/17
[/TD]
[TD]44
[/TD]
[TD]PR
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CF550D
[/TD]
[TD][/TD]
[TD]32
[/TD]
[TD]MP
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]UM553W
[/TD]
[TD]8/14/17
[/TD]
[TD][/TD]
[TD]FS
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4T67DP
[/TD]
[TD]10/2/17
[/TD]
[TD]12
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










What I need to do is throw a pop-up that says there's an error for every row that has B populated but has a blank cell in rows A, C, D, and/or E.

I don't even know how to start. Thanks so much to you Excel gurus!!!

 

[TresRing]

By the way, this wrong code is what I have come up with, but it auto-populates ERROR! in every cell when column B is filled. What I want is for a pop up to tell me there is an error because those cells aren't filled when B is. I really don't know how to change it to make it work.

Sub IfCellsBlank()

For Each cel In Range("B1:B9999")
    If cel.Value <> "" Then cel.Offset(0, -1).Value = "ERROR! A"
    If cel.Value <> "" Then cel.Offset(0, 1).Value = "ERROR! C"
    If cel.Value <> "" Then cel.Offset(0, 2).Value = "ERROR! D"
    If cel.Value <> "" Then cel.Offset(0, 3).Value = "ERROR! E"
    If cel.Value <> "" Then cel.Offset(0, 4).Value = "ERROR! F"
    If cel.Value <> "" Then cel.Offset(0, 5).Value = "ERROR! G"
Next

End Sub

 

[Fluff]

If you have a lot of blanks then there could be a problem trying to give you all the info.
so this is another option, it will simply highlight the blank cells red

Sub BlankCells()

    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    Range("A1").CurrentRegion.SpecialCells(xlBlanks).Interior.Color = vbRed
    Columns(2).AutoFilter

End Sub

 

[TresRing]

If you have a lot of blanks then there could be a problem trying to give you all the info.
so this is another option, it will simply highlight the blank cells red

Sub BlankCells()

    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    Range("A1").CurrentRegion.SpecialCells(xlBlanks).Interior.Color = vbRed
    Columns(2).AutoFilter

End Sub

OMG! That works and is an even better idea than mine because it's very visual on the location of the missing data. THANK YOU!!!!!!!!!!!!!!!!!!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[TresRing]

I'm back because I have two errors.

1) After I resolve the missing info and rerun the macro, the red squares don't go away.
2) Once I resolve ALL cells and run the macro, it collapses all the cells above the first cell. I started with column B12.

Sub BlankCells()

    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    Range("B12").CurrentRegion.SpecialCells(xlBlanks).Interior.Color = vbRed
    Columns(2).AutoFilter

End Sub

Is there something I can do about these two problems?

Thanks. You're getting a workout today!

 

[Fluff]

I'm not that surprised you had problems, as what I supplied was more an idea, than a solution.
If your header row is row 12 try

Sub BlankCells()

    Cells.Interior.Color = xlNone
    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    On Error Resume Next
    Range("B12").CurrentRegion.SpecialCells(xlVisible).SpecialCells(xlBlanks).Interior.Color = vbRed
    On Error GoTo 0
    Columns(2).AutoFilter

End Sub

 

[TresRing]

I'm not that surprised you had problems, as what I supplied was more an idea, than a solution.
If your header row is row 12 try

Sub BlankCells()

    Cells.Interior.Color = xlNone
    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    On Error Resume Next
    Range("B12").CurrentRegion.SpecialCells(xlVisible).SpecialCells(xlBlanks).Interior.Color = vbRed
    On Error GoTo 0
    Columns(2).AutoFilter

End Sub

The headers have colored cells, and I didn't want those changed so I reordered the no-color-cell line to fall after it checks for errors and now it seems to do what I need.

Sub BlankCells()

    Columns(2).AutoFilter field:=1, Criteria1:="<>"
    On Error Resume Next
    Cells.Interior.Color = xlNone
    Range("B12").CurrentRegion.SpecialCells(xlVisible).SpecialCells(xlBlanks).Interior.Color = vbRed
    On Error GoTo 0
    Columns(2).AutoFilter

End Sub

The above is an actual solution that I linked to a refresh button and seems to work like a charm. Thank you.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

Here is another macro for you to consider (it eliminates the slight flash using the AutoFilter produces)...

[table="width: 500"]
[tr]
	[td]Sub BlankCells()
  On Error GoTo NoBlanks
  Cells.Interior.Color = xlNone
  Intersect(Columns("B").SpecialCells(xlConstants).EntireRow, Range("B12").CurrentRegion.SpecialCells(xlBlanks)).Interior.Color = vbRed
NoBlanks:
End Sub[/td]
[/tr]
[/table]