Check cells in 3 columns against each other, then highlight empty cell

[cjtraas]

Hi. I need to build a macro to check if cells in 3 columns contain anything in a sheet, then highlight an empty cell. I've been searching for solutions and am unable to find something that really fits what I need.

I would like to check columns B and C against A. For example, if B6 contains text, then A6 should also contain text (and then the cell highlighted) - but C6 does not need to contain text. OR- If C6 contains text, then A6 should also contain text (but not necessarily B6) and then A6 should be highlighted. Also, if both B6 and C6 contain text, then A6 should contain text. I do not need to check the entire columns (A:A)(B:B)(C:C). Rows 1-50 should be sufficient if that makes any difference for performance or efficiency.

After I get this part figured out, I'm going to try to automatically fill in the text that is supposed to be put in A. For example, if B6 contains text, then A6 should autofill with "B". If C6 contains text, the A6 should autofill with "C" - AND if both B6 and C6 contain text, then A6 should autofill with "B and C".

Any help or pointers in the right direction would be much appreciated. I've done a bit of VBA in the past, so I'm hoping to at least get a hint or some direction - no need to write the whole thing for me if it's too involved. Thanks!

 

[kevin9999]

I'm struggling to follow your logic with this:

then highlight an empty cell.

and this:

For example, if B6 contains text, then A6 should also contain text (and then the cell highlighted)

which cell do you want highlighted in the case you describe - B6 or A6?

Could you provide a small sample showing examples of different cases using the XL2BB add in so we can follow what you mean?

 

[kevin9999]

Just to get the ball rolling, this may help the second part of your question. (Conditional Formatting should fix the first part so the whole can be done without a macro).

Book1
	A	B	C
1
2	B	x
3
4	C		x
5
6	B and C	x	x
7
8
9	B	x
10
11	C		x
12
13	B and C	x	x
14
15
16	B	x
17	B	x
18	C		x
19	C		x
20
21
22	B and C	x	x
23	B and C	x	x
Sheet1

Cell Formulas
Range	Formula
A1:A23	A1	=IF(AND(B1<>"",C1<>""),"B and C",IF(B1<>"","B",IF(C1<>"","C","")))

 

[cjtraas]

I'm struggling to follow your logic with this:

and this:

which cell do you want highlighted in the case you describe - B6 or A6?

Could you provide a small sample showing examples of different cases using the XL2BB add in so we can follow what you mean?

Thanks for the reply, and I apologize for the confusion (it was a long day...).

First, I would highly prefer this to be in VBA, since the users have had a tendency to "accidentally reformat" the source Workbook. I would have to continually redo the Conditional Formatting.

To answer the question / confusion, I'm attaching an example using the XL2BB add in. Rows 1-3 show what is supposed to be entered. The users are supposed to enter info into B and C, and then different users enter info into A - but those users sometimes miss it. Then they tell me that it is "completed", where I have to go through and check that everything is entered correctly. I just want to make that happen easily and automatically. So, I would like to highlight they A cells that are supposed to have info entered. Rows 5-7 show what I want to have happen if they fail to enter info into A.

Book1
	A	B	C
1	This Text	Text
2	That Text		Text
3	This and That	Text	Text
4
5		Text
6			Text
7		Text	Text
Sheet1

 

[kevin9999]

If you still want to:

try to automatically fill in the text that is supposed to be put in A

then try the following in the worksheet code area of the relevant sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B:C"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim LRow As Long, c As Range
        LRow = Range("A:C").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        
        For Each c In Range("A1:A" & LRow) '<< Change A1 to A2 if you have headers on your actual sheet
            If c.Offset(, 1) <> "" And c.Offset(, 2) <> "" Then
                c = c.Offset(, 1) & " and " & c.Offset(, 2)
            Else
                c = c.Offset(, 1) & c.Offset(, 2)
            End If
        Next c
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Which I think might eliminate the need to highlight where text is missing in column A. Alternatively, if you just want to highlight cells in A where there is nothing entered - but there is some text in B and/or C, then try the following code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A:C"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim LRow As Long, c As Range
        LRow = Range("A:C").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        Range("A1:A" & LRow).Interior.Color = xlNone
        For Each c In Range("A1:A" & LRow) '<< Change A1 to A2 if you have headers on your actual sheet
            If c.Offset(, 1) <> "" Or c.Offset(, 2) <> "" Then
                If c = "" Then c.Interior.Color = vbYellow
            End If
        Next c
    End If
Continue:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

In both cases, right click the sheet tab, select "view code", and copy the code above to the window that appears on the right. Save the file & test.

 

[DanteAmor]

The users are supposed to enter info into B and C

With the following code, in the events of your sheet, when users write a value in B or C, automatically, the code checks the cell in column A, if it is empty then it highlights it in yellow.
Also if you delete the data from B and C, the color disappears.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Range("B1:C" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      With Range("A" & c.Row)
        If c.Value <> "" And .Value = "" Then
          .Interior.Color = vbYellow
        ElseIf Range("B" & c.Row).Value = "" And Range("C" & c.Row).Value = "" Then
          .Interior.Color = xlNone
        End If
      End With
    Next
  End If
End Sub

------------------------------------------

If you don't want it to be automatic, then run this macro.

Sub Macro1()
  Dim lr As Long
  Application.ScreenUpdating = False
 
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  lr = Range("A:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  With ActiveSheet.Range("A1:C" & lr)
    .AutoFilter Field:=2, Criteria1:="<>"
    .AutoFilter Field:=1, Criteria1:="="
    lr = Range("A:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lr > 1 Then Range("A2:A" & lr).Interior.Color = vbYellow
    ActiveSheet.ShowAllData
   
    .AutoFilter Field:=3, Criteria1:="<>"
    .AutoFilter Field:=1, Criteria1:="="
    lr = Range("A:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lr > 1 Then Range("A2:A" & lr).Interior.Color = vbYellow
    ActiveSheet.ShowAllData
  End With
  Application.ScreenUpdating = True
End Sub

------------------------------------------
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
------------------------------------------

 

[cjtraas]

Thanks for the replies. I've been fighting a bad cold for several days and still won't be able to try these for a while. I'll come back and update though. Thanks again.