How to highlight row if same person has two different plan names

[kmsprague]

Hi all,
I'm trying to highlight a row if a person has two different types of HSA plan. The list has other plan names, so I'm unable to use a unique highlight rule in conditional formatting. I've tried =COUNTIFS($A$2:$A$3000,$A3,$C$2:$C$3000,"*HSA - F*",$C$2:$C$3000,"*HSA - EE*") but it didn't work. I'm sure I'm missing something simple, but basically, I just want to have a formula that looks for the person's employee number in column A and then highlights if they have both types of HSA plans listed in column C. All help is greatly appreciated!!

Book2
	A	B	C
1	Employee Number	Employee Name	Plan Name
2	12345	Smith, John	HSA - Family
3	67890	Smith, John	HSA - EE Only
Sheet1

 

[mikenelena]

I'm unsure how to highlight the rows without resorting to VBA code.

You could place this formula into a column and it will indicate the number of plans an employee has:

=IF(COUNTIFS(A:A,A2,C:C,"HSA - Family")>0,IF(COUNTIFS(A:A,A2,C:C,"HSA - EE Only")>0,"Two Plans","One Plan"),"No Plan")

If you don't mind using VBA, this might accomplish what you are looking to do:

Sub Highligh_2HSA()
    Dim rngCell As Range
    Dim rngCurrentCell As Range
    Dim lRow As Long
    Dim sEmployee As String
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each rngCell In Range("A2:A" & lRow)
        sEmployee = rngCell.Value
        
        For Each rngCurrentCell In Range("A2:A" & lRow)
            If sEmployee = rngCurrentCell.Value And rngCurrentCell.Offset(0, 2) <> rngCell.Offset(0, 2) Then
                rngCell.EntireRow.Interior.ColorIndex = 6
                rngCurrentCell.EntireRow.Interior.ColorIndex = 6
            End If
        Next rngCurrentCell
    Next rngCell
End Sub

 

[Fluff]

How about

=AND(COUNTIFS($A$2:$A$3000,$A3,$C$2:$C$3000,"*HSA - F*"),COUNTIFS($A$2:$A$3000,$A3,$C$2:$C$3000,"*HSA - EE*"))

 

[kmsprague]

How about

=AND(COUNTIFS($A$2:$A$3000,$A3,$C$2:$C$3000,"*HSA - F*"),COUNTIFS($A$2:$A$3000,$A3,$C$2:$C$3000,"*HSA - EE*"))

Thank you so very much!! Entering this into conditional formatting did the trick!

 

[kmsprague]

I'm unsure how to highlight the rows without resorting to VBA code.

You could place this formula into a column and it will indicate the number of plans an employee has:

=IF(COUNTIFS(A:A,A2,C:C,"HSA - Family")>0,IF(COUNTIFS(A:A,A2,C:C,"HSA - EE Only")>0,"Two Plans","One Plan"),"No Plan")

If you don't mind using VBA, this might accomplish what you are looking to do:

Sub Highligh_2HSA()
    Dim rngCell As Range
    Dim rngCurrentCell As Range
    Dim lRow As Long
    Dim sEmployee As String
   
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    For Each rngCell In Range("A2:A" & lRow)
        sEmployee = rngCell.Value
       
        For Each rngCurrentCell In Range("A2:A" & lRow)
            If sEmployee = rngCurrentCell.Value And rngCurrentCell.Offset(0, 2) <> rngCell.Offset(0, 2) Then
                rngCell.EntireRow.Interior.ColorIndex = 6
                rngCurrentCell.EntireRow.Interior.ColorIndex = 6
            End If
        Next rngCurrentCell
    Next rngCell
End Sub

Thank you so much! I tried this and it looked like it highlighted the cells that didn't contain HSA. I'm not well versed in VBA, so I don't know how to fix it.

 

[Fluff]

Glad we could help & thanks for the feedback.