Conditional Formatting with "Or" logical operator

Question

Hello,

I'm having some issues with conditional formatting.

I have the "D69" cell in the "Foglio2" with the following data validation:

• Allow: list
• Source:  =Foglio3!$N$2:$N$5

In the cell range N2:N5 of the "Foglio3" I have the following text values:

• Minisito / Landing --> cell N2
• App --> cell. N3
• Progettazione UX/IA --> cell. N4
• Campagna Banner --> cell N5

In the "Foglio2" I have the following VBA code:

---

Option Explicit

' Developed by Contextures Inc.

' www.contextures.com

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngDV As Range

Dim oldVal As String

Dim newVal As String

Dim lUsed As Long

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then

   'do nothing

Else

  Application.EnableEvents = False

  newVal = Target.Value

  Application.Undo

  oldVal = Target.Value

  Target.Value = newVal

  If Target.Address = "$D$69" Or Target.Address = "$H$135" Or Target.Address = "$E$139" Or Target.Address = "$E$143" Or Target.Address = "$H$151" Or Target.Address = "$H$155" Or Target.Address = "$H$189" Or Target.Address = "$H$193" Then

    If oldVal = "" Then

      'do nothing

      Else

      If newVal = "" Then

      'do nothing

      Else

        lUsed = InStr(1, oldVal, newVal)

        If lUsed > 0 Then

            If Right(oldVal, Len(newVal)) = newVal Then

                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)

            Else

                Target.Value = Replace(oldVal, newVal & ", ", "")

            End If

        Else

            Target.Value = oldVal _

              & ", " & newVal

        End If

      End If

    End If

  End If

End If

exitHandler:

  Application.EnableEvents = True

End Sub

---

With the above code, user can select a single or multiple items by the dropdown menu of the D69 cell, e.g.:

• can select jus "Minisito / Landing" so that the cell returns "Minisito / Landing"
• can select "Minisito / Landing" and then "App" so that the cell returns "Minisito / Landing, App"
• can select "App" and then "Minisito / Landing" so that the cell returns "App, Minisito / Landing"
• can select "Minisito / Landing" and then "App" and then "Progettazione UX/IA" so that the cell returns "Minisito / Landing, App, Progettazione UX/IA"
• etc...

I want that when user make a choise in the above drop down menu (see examples above), other cells in the same sheet being formatted in a certain way.

I've used the following rules in the conditional formatting and they all works:

---

• =$D$69=""
• =$D$69="Minisito / Landing"
• =$D$69="App"
• =$D$69="Progettazione UX/IA"
• =$D$69="Campagna Banner"
• =O($D$69="Minisito / Landing, App";$D$69="App, Minisito / Landing")
• =O($D$69="Minisito / Landing, Progettazione UX/IA";$D$69="Progettazione UX/IA, Minisito / Landing")
• =O($D$69="Minisito / Landing, Campagna Banner";$D$69="Campagna Banner, Minisito / Landing")

---

The only one that does not work is the following one:

=O($D$69="Minisito / Landing, App, Progettazione UX/IA";$D$69="Minisito / Landing, Progettazione UX/IA, App";$D$69="App, Minisito / Landing, Progettazione UX/IA;$D$69="Progettazione UX/IA, Minisito / Landing, App";$D$69="Progettazione UX/IA, App, Minisito / Landing").

When I try creating it, I get an errore message: "The rule has an error...etc.."

I can't understand what the error is due to.

Anyway, I tried to replace the text values in the rules above with the absolute references, e.g.:

---

• =$D$69=$N$2
• =$D$69=$N$3
• =$D$69=$N$4
• =$D$69=$N$5

---

They all works as well.

The problem is that when I try using the "Or" logical operator, they do not work.

I've tried with the following ones:

---

• =O($D$69="Foglio3!$N$6, Foglio3!$N$7";$D$69="Foglio3!$N$7, Foglio3!$N$6") --> conditional formatting is not applied
• =O($D$69=Foglio3!$N$6, Foglio3!$N$7;$D$69=Foglio3!$N$7, Foglio3!$N$6) --> I get the error msg "The rule has an error...etc.."
• =O($D$69=Foglio3!$N$6; Foglio3!$N$7;$D$69=Foglio3!$N$7; Foglio3!$N$6) --> it works just when user select one single value in the drop down menu
• =O($D$69=(Foglio3!$N$6, Foglio3!$N$7);$D$69=(Foglio3!$N$7; Foglio3!$N$6)) --> I get the error msg "The rule has an error...etc.."
• =O[$D$69=(Foglio3!$N$6, Foglio3!$N$7);$D$69=(Foglio3!$N$7; Foglio3!$N$6)] --> I get the error msg "The rule has an error...etc.."

---

Also in these cases, I can't understand what the problem is due to. I've spent a lot of time searching for solutions on blog/forum, etc.. but unsuccesfully.

Could you please provide me with any suggestions?

Thanks in advance.

Roberto

Answer 1

In the case of the long formula which does not work in conditional formatting there is a missing ", underlined below: 

=O($D$69="Minisito / Landing, App, Progettazione UX/IA";$D$69="Minisito / Landing, Progettazione UX/IA, App";$D$69="App, Minisito / Landing, Progettazione UX/IA**"**;$D$69="Progettazione UX/IA, Minisito / Landing, App";$D$69="Progettazione UX/IA, App, Minisito / Landing").

In the cases where you are using cell references, they should not be enclosed in quotes - the formulas are looking for the strings, e.g. "Foglio3!$N$6, Foglio3!$N$7".  What you want to look for is the contents of those cells separated by ", " .  Like this:

=O($D$69=Foglio3!$N$6 & ", " & Foglio3!$N$7;$D$69=Foglio3!$N$7 & ", " & Foglio3!$N$6)

Answer 2

Thanks a lot for your support!

They works.

Best,

Roberto