PDA

View Full Version : [SOLVED] Need advice on VBA Conditional formatting Formula : Run-time error '5'



chakkrav
07-23-2019, 09:27 PM
I’m trying to compare 2 files using conditional formatting & getting error at highlighted place & unable to figure out where I’m wrong. Could anyone advise on the below error!

Sub pre_post()
Dim shtName As String
shtName = Application.InputBox("Please type in the sheet name you want to compare it with:", "Compare", , , , , , 2)
If shtName = "false" Then Exit Sub
ActiveSheet.UsedRange.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(A1='" & shtName & "'!""A1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Color = -16711681
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveSheet.Range("A1").Select
End Sub


Getting error at this location:

24661

and the error is :

24662

Bob Phillips
07-24-2019, 03:08 AM
Too many quotes. Try


Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(A1='" & shtName & "'!A1)"