VBA: Cbeck cell if empty, dependent on other cells

JennV

New Member
Joined
May 9, 2019
Messages
34
Header1
Header2
Header3
Cat

<tbody>
</tbody>

Hello there,

I currently have this code:

Set CheckC = Range("B2")
If Range("A2") = Value And Range("B2") = "" Then
MsgBox "You cannot transfer until required information has been completed." & _
vbCrLf & vbNewLine & "Check for cells with light blue markings (All cells highlighted in light blue must be completed.)"
CheckC.Select
If Range("A2") = Value And IsEmpty(CheckC) Then Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End If

My goal is, if the user has data in column A, then the corresponding B column must be filled out. So, if A2 is filled out, then B2 must also be filled out and this is applied to the entire set of data. Any help is much appreciated! I'm not VBA-savvy so if the code above is not making sense (well, obviously it's not because it's not working), please forgive me...
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
631
Hi JennV - Welcome to the forum. Maybe this will help get things started. Please explain what happens when you run your code. That may help us help you. Hope this helps.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this "Worksheet_Change" event :-
Paste entire code in Worksheet Module (Alt + F11")

Code:
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = IIf(Rng.Address(0, 0) = "A1:A2", Range("A2"), Rng)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn <> "" And Dn.Offset(, 1) = "" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Target.Address = Dn.Address [COLOR="Navy"]Then[/COLOR]
        MsgBox "Please enter data in " & Dn.Offset(, 1).Address
        Application.EnableEvents = False
            Target = ""
        Application.EnableEvents = True
    [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,258
Messages
5,467,616
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top