Conditional Formatting Problem - Highlight if multiple of select cells are populated

[grreyes24]

Attached is a picture showing what I'm trying to accomplish but I'm not quite getting it.

first column has 20 roles listed down the rows. 5 of them are "conflicting" A2, A7, A12, A16, and A20 (I highlighted these in yellow, but not needed on the formatting). A user can select none or any one of these roles, but not two or more. If the user does select multiple of these roles, I would like to highlight the conflicting cells (red on sample).

So it's not just that the cell is populated, but that other particular cells are populated as well.

 

[kweaver]

Would you change your profile, please, to indicate what version of Excel you run and post your example using XL2BB ?!

 

[kweaver]

Off the top of my head I couldn't think of a formula to handle this but came up with this:

Sub highlgt()
Dim str1 As String, ary() As String, i As Long
Dim icol As Long, icol2 As Long

For icol = 2 To 5
icol2 = icol - 1
str1 = ""
If Cells(3, icol) = "x" Then
 str1 = str1 & WorksheetFunction.Choose(icol2, "B", "C", "D", "E", "F") & "3 "
 Else
 End If
If Cells(8, icol) = "x" Then
 str1 = str1 & WorksheetFunction.Choose(icol2, "B", "C", "D", "E", "F") & "8 "
 Else
 End If
If Cells(13, icol) = "x" Then
 str1 = str1 & WorksheetFunction.Choose(icol2, "B", "C", "D", "E", "F") & "13 "
 Else
 End If
If Cells(17, icol) = "x" Then
 str1 = str1 & WorksheetFunction.Choose(icol2, "B", "C", "D", "E", "F") & "17 "
 Else
 End If
If Cells(21, icol) = "x" Then
 str1 = str1 & WorksheetFunction.Choose(icol2, "B", "C", "D", "E", "F") & "21 "
 Else
End If

ary = Split(str1)
If 1 < UBound(ary) Then
For i = 0 To UBound(ary) - 1
Range(ary(i)).Interior.ColorIndex = 19
Next i
Else
End If
Next icol
End Sub