AND(OR)) Formatting function troubleshoot

[Rishek]

This line is giving me a compile error "expected: end of statement".

.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR$A1="Romano",$A1="De Vita",$A1="Monta",$A1="Ballentine",$A1="Ott",$A1="Holiday",$A1="Skinner",$A1="Crocetto",$A1="Ngqungwana",$A1="Garrett",$A1="Duval",$A1="Woods",$A1="Trevigne",$A1="Becerra",$A1="Burden"),$G1>TIMEVALUE("6:00"),$G1<>"")"

I'm just trying to use it to add a highlight to a set of hh:mm times if the time is over 6:00 hours. Some of the cells in the column are blank. It works in Excel (returning the correct true false values). There's is likely a better way to do this. That'd be totally welcome, but I'd also like to know what's wrong with how I'm entering this formula in the VBA editor for the future.

Thanks!

[SamT]

Check for matching quotes and Parentheses and Commas

[mdmackillop]

Missing bracket here OR$A1

[Rishek]

Thanks for the bracket. Oddly, it's in the thing I saved, so I don't know how I copied it wrong here. Still getting the same error.

Even number of quotes, brackets all open and close.

Formula works in excel, just not in the editor. I'm at a loss.

[mdmackillop]

Such code is hard to maintain if names are subject to change, Probably better to keep them as a list in the workbook and use a Match comparison. It may be simpler to set colours in the code. Maybe run an event code when A1 is changed.

[Rishek]

So now I have an extra worksheet called Guests with the needed name in column A.

=AND(MATCH(A:A,Guests!A:A,0),G:G>TIMEVALUE("6:00"),G:G<>"")

Code looks like this:

        With .Columns("G:G")
              .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(MATCH(A:A,Guests!A:A,0),G:G>TIMEVALUE("6:00"),G:G<>"")"
              .FormatConditions(1).SetFirstPriority
              With .FormatConditions(1).Interior
                  .PatternColorIndex = xlAutomatic
                  .Color = 255
                  .TintAndShade = 0
              End With
              .Range("G1").FormatConditions.Delete
        End With

Same error, but now it highlights the number 6. When I plug the formula it gives me an N/A error unless there's a match in the A column in which case it gives me a true/false value.

[Bob Phillips]

Double up on embedded quotes

With .Columns("G:G")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(MATCH(A:A,Guests!A:A,0),G:G>TIMEVALUE(""6:00""),G:G<>"""")"
    .FormatConditions(1).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    .Range("G1").FormatConditions.Delete
End With

[Rishek]

Thanks for this. Of course now the formula doesn't work. I've reverted to the old hard-coded list of names with the added double quotes and it seems to be doing fine.