Excel 2013 Nesting IF functions???

[IIA132]

I am working on a Separation of Duties Form and I need a way to highlight or strike through a name if it is showing in multiple columns (Duty 1, Duty 2, Duty 3). The Form has 3 columns. The goal is if a name (Karen) is assigned in all three columns then we would like either a strike through her name in the column that creates conflict or a highlight in red indicating that the Duties assigned conflict. However, As an example, Duty 1 and Duty 3 can be assigned to the same person, but the person cannot do all three duties (which is what Karen is doing).

I wanted to send an attachment of the spreadsheet I am working on, but it seems I am unable to post attachments?

 

[horrellbt01]

Take a look at this conditional formatting solution:

https://www.mrexcel.com/forum/excel...ting-check-3-more-cells-equal-each-other.html

 

[jtakw]

Hi,

May be something like this for CF formula rule:

=COUNTIF(A1:C1,"Karen")=3

You can change "Karen" to a cell reference if you like.

 

[jim may]

Paste the below CODE into your Sheet Module versus a Standard Module..

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Columns("A:C")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
TR = Target.Row
MyCount = 1
Set Rng = Range(Cells(TR, 1), Cells(TR, 3))
Duty1 = Rng(1).Value
    For i = 2 To 3
        If Cells(TR, i).Value = Duty1 Then
        MyCount = MyCount + 1
        End If
    Next i
If MyCount = 3 Then
    MsgBox "You have entered " & Target.Value & " three times. Try someone Else."
Target.Value = ""
End If
Application.EnableEvents = True
End Sub

Excel 2010

	A	B	C
Duty 1	Duty 2	Duty 3
Karen	Karen

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​