VBA - Highlight Blank Cell

Experts,

Code/workbook attached.

I am trying to get the following code to work:

' Check for blank MAILCODE entries.
            For Each cell In Range([H2], [H2].End(xlDown))
                If cell = vbNullString Then
                    cell.Interior.ColorIndex = 6
                End If
            Next cell

Open in new window


I also tried changing the above to:

If cell = "" Then

Open in new window


On sheet INPUT DATA I have removed the values in cells H4 and H25. Not sure if the errors (numbers stored as text) in column H impacts the type of cell value. H4 was initially stored as a number and H25 was a number stored as text.

So H4 and H25 should be colored Yellow as they are blank.

TargC is updating on the blank cells in the following:

' TargC counting of blank Mailcodes in MAILCODE
    TargC = 0
    Set rng = [H2]
    Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column))
        For Each cell In rng
            If cell = "" Then
                TargC = TargC + 1
            End If
        Next cell

Open in new window



Thank you,
Kevin
Sub M_Errors()

Dim cell As Range
Dim rng As Range
Dim TargA, TargB, TargC As Variant
Dim strTargA, strTargB, strTargC As String


' Sets all Cell ColorIndex to xlNone (Clear) _
  As this sub may be re-ran after corrections are made by user.
        Sheets("INPUT DATA").Activate
            For Each cell In Sheets("INPUT DATA").UsedRange
                If cell.Interior.ColorIndex <> xlNone Then
                    cell.Interior.ColorIndex = xlNone
                End If
            Next cell
            
' Clears null strings from GM DATABASE IMPORT so blank cells can be identified.
            For Each cell In Sheets("INPUT DATA").UsedRange
                If Len(cell) = 0 And Len(cell.Formula) = 0 Then cell = vbNullString
            Next cell
    
' Check for GROUPSIZE errors, where data is anything but "M" _
  Array utilized as more GROUPSIZE values are likely to be added.
            For Each cell In Range([C2], [C2].End(xlDown))
                If IsError(Application.Match(cell, Array("M"), 0)) Then
                    cell.Interior.ColorIndex = 3
                End If
            Next cell
            
' Check for PREMIUM errors, where data is less than 1.
            For Each cell In Range([F2], [F2].End(xlDown))
                If cell.Value < 1 Then
                    cell.Interior.ColorIndex = 4
                End If
            Next cell

' Check for blank MAILCODE entries.
            For Each cell In Range([H2], [H2].End(xlDown))
                If cell = vbNullString Then
                    cell.Interior.ColorIndex = 6
                End If
            Next cell
            
' TargA counting of "non" M cells in GROUPSIZE
    TargA = 0
    Set rng = [C2]
    Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column))
        For Each cell In rng
            If cell <> "M" Then
                TargA = TargA + 1
            End If
        Next cell

' TargB counting of "0" Premium in PREMIUM
    TargB = 0
    Set rng = [F2]
    Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column))
        For Each cell In rng
            If cell < 1 Then
                TargB = TargB + 1
            End If
        Next cell

' TargC counting of blank Mailcodes in MAILCODE
    TargC = 0
    Set rng = [H2]
    Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column))
        For Each cell In rng
            If cell = "" Then
                TargC = TargC + 1
            End If
        Next cell

' To Display Message Box or not
    Select Case TargA
        Case 0
            ' skip to TargB
        Case 1
            strTargA = "There Is " & TargA & " Contract Not Marked As 'M' - For Medicare" & vbCrLf & vbCrLf
        Case Else
            strTargA = "There Are " & TargA & " Contracts Not Marked As 'M' - For Medicare" & vbCrLf & vbCrLf
    End Select
    Select Case TargB
        Case 0 ' skip to TargC
        Case 1
        strTargB = "There Is " & TargB & "Contract Without A Premium Value" & vbCrLf & vbCrLf
        Case Else
        strTargB = "There Are " & TargB & " Contracts Without Premium Values" & vbCrLf & vbCrLf
    End Select
    Select Case TargC
        Case 0 ' skip to string assembly for message box
        Case 1
        strTargC = "There Is " & TargC & "Contract Without A Mailcode" & vbCrLf & vbCrLf
        Case Else
        strTargC = "There Are " & TargC & " Contracts Without Mailcodes" & vbCrLf & vbCrLf
    End Select
    
' Then I want to assemble the above strings, if not null such as:
    If TargA + TargB + TargC = 0 Then
        MsgBox "GM Data Imported Successful With No Formatting Errors"
            Else
        MsgBox strTargA + strTargB + strTargC + "Please Correct The Above Errors In GM And Re-Query The Data"
    End If

End Sub

Open in new window

EE---VBA---Dynamic-MsgBox-ver2.xls
LVL 23
redrumkevAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You need to alter that first code to:
for each cell in Range([H2], Cells(Cells(Rows.Count, 1).End(xlUp).Row, "H"))

Open in new window


If you use End(xldown) from H2 and H4 is blank, then the range you check will only be H2:H3 since the end(xldown) stops before the first blank cell...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redrumkevAuthor Commented:
rorya,

I can't believe I didn't catch that, I was so worried that I wasn't searching the range for the correct form of "blank", never did I think to look and see if my range actually included the blank cells!

I also changed this in the other two For Each loops, just in case this data should come out of the DB blank, thus the range will extend to the bottom, not stop at the blank.

On a side note, I had mistyped strTargC, accidentally putting it in as strTragC. Is there something I should do within the VBE to test for this or some sort of best practice that I should follow when adding/changing/inputting variables?
0
Rory ArchibaldCommented:
You should always have Option Explicit at the top of every module - it forces you to declare all variables, so it would catch a typo like that. You can set this to be the default for all new modules by checking the 'Require variable declaration' option in Tools-Options in the VB Editor.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

harr22Commented:
Are you trying to highlight all the blanks?

How about doing it like this

 
For Each cell In Range([H2], [H65536].End(xlUp))
        If cell = vbNullString Then
            cell.Interior.ColorIndex = 6
        End If
    Next cell

Open in new window

0
redrumkevAuthor Commented:
Ok - that is great, I will set that up!

Also, I was just messing around with the code, which worked great. I noticed that it looks down column 1 to find an end. Is there a way I can modify this so that the last row is detected. Example, if A47 was blank, then it would only see H2:H46, however there is data in H2:50.

Just trying to trouble shoot this now before the user ends up with a mess on their hands!

And that is the final add-on question, I promise :)

Kevin
0
redrumkevAuthor Commented:
Rorya,

Or should I use harr22's suggestion, which removes the possiblity of a blank in A1:A(end) causing a range issue?

Is there a performance issue with the large range search:
For Each cell In Range([H2], [H65536].End(xlUp))

Open in new window


Kevin
0
Rory ArchibaldCommented:
The code looks UP column A, not down, so as long as you have data for every row in A, it will be fine.
harr22's code would not work if you had gaps at the end of your data that ought to be flagged, since it would ignore them.
Note you could also use something like this instead of looping:
On Error Resume Next
Range([H2], Cells(Cells(Rows.Count, 1).End(xlUp).Row, "H")).Specialcells(xlcelltypeblanks).interior.colorindex = 6
On Error Goto 0

Open in new window


this assumes the cells are actually blank, rather than having say "" as the result of a formula.
0
harr22Commented:
65536 was the last row in earlier versions of excel so I use that as the anchor for the starting point, typically you wouldn't have data beyond that.  if you are using the new version you could change that to 1048576.

Rory is doing the same thing, but testing to see what the last row actually is (looking at column A).

in the end, the range thats getting evaluated is the same either way.
0
redrumkevAuthor Commented:
rorya,

Ok - I think I am going to stick with what I have got, reason being that without a value in column A, which in the DB is accountno, no data will be pulled to begin with, so column A is a prerequisite for everything else on the line.

I did turn the tools-option to 'Require variable declaration' on, and added "Option Explicit" before my sub. That is great, because it took me right to the variable that was misspelled and not declared!

THANK YOU ONCE AGAIN!!!  A+
Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.