PDA

View Full Version : [SOLVED] AND(OR)) Formatting function troubleshoot



Rishek
06-16-2017, 09:41 AM
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
06-16-2017, 10:21 AM
Check for matching quotes and Parentheses and Commas

mdmackillop
06-16-2017, 10:40 AM
Missing bracket here OR$A1

Rishek
06-16-2017, 11:00 AM
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
06-16-2017, 11:19 AM
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
06-16-2017, 12:14 PM
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
06-16-2017, 02:58 PM
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
06-17-2017, 12:45 PM
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.