Search code examples
excelexcel-formulais-empty

Excel Macro check if cell is empty and search specific word in column

Guy, I am beginner for VBA language, and I have a question to stuck on it.

How to make a macro script to check if ANY rows of column B is input word of "C" AND ANY rows of column C is empty, then it will trigger to highlight this row with color and prompt up the message box to remind user to correct it.

Also, the column D is using the formula and cell by cell method to check the above requirement.

=IF(ISBLANK(B4),"",IF(OR(B4="C",B4="O"),IF(AND(B4="C", ISBLANK(C4)),"WARNING: Case Closed! Please Write Down Resolution!",""),"ERROR: Invalid Value - Status! Please Input The Right Value!"))

For example, the row 4 meet up requirement and affected.

Is there way to do so? Please help. Thanks.

Pic

UPDATE:Thanks Variatus!

When I save the file, it prompt up this message box. What can I do? Thanks.

Macro Screen

screen

Error

error

Solution

  • Under normal circumstances you would be asked to show more of an own effort before receiving help on this forum, including from me. But apparently circumstances aren't normal. So, here we go. Paste this procedure to a standard code module (it's name would be a variation of Module1 by default).

    Option Explicit

Sub MarkErrors()
    ' 283
    
    Dim Spike()         As String
    Dim i               As Long                 ' index of Spike
    Dim Rl              As Long                 ' last used row
    Dim R               As Long                 ' loop counter: rows

    Application.ScreenUpdating = False
    With Sheet1                                 ' this is the sheet's CodeName (change to suit)
        .UsedRange.Interior.Pattern = xlNone    ' remove all existing highlights
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        ReDim Spike(1 To Rl)
        For R = 2 To Rl
            If Trim(.Cells(R, "B").Value) = "C" Then
                If IsEmpty(.Cells(R, "C")) Then
                    .Range(.Cells(R, "A"), .Cells(R, "D")).Interior.Color = vbYellow
                    i = i + 1
                    Spike(i) = "Row " & R
                End If
            End If
        Next R
    End With
    Application.ScreenUpdating = True
    
    If i Then
        ReDim Preserve Spike(1 To i)
        MsgBox "Status errors were found in the following entries:-" & vbCr & _
               Join(Spike, "," & vbCr), vbInformation, "Corrections required"
    End If
End Sub

    Pay attention to the specified worksheet Sheet1. This is a CodeName, and it is a default. Excel will create a sheet by that name when you create a workbook. The CodeName doesn't change when the user changes the tab name but you can change it in the VB Editor. It's the (Name) property of the worksheet.

    Install the procedure below in the code sheet of Sheet1 (not a standard code module and therefore not the same as where you installed the above code. This module is created by Excel for each sheet in every workbook. Use the existing one.

    Private Sub Worksheet_Activate()
    ' 283
    MarkErrors
End Sub

    This is an event procedure. It will run automatically whenever Sheet1 is activated (selected). So, under normal circumstances you shouldn't ever need to run the first procedure manually. But I've already talked about circumstances. They aren't always normal. :-)