Consulting

Results 1 to 2 of 2

Thread: Need advice on VBA Conditional formatting Formula : Run-time error '5'

  1. #1
    VBAX Regular
    Joined
    Jan 2008
    Posts
    15
    Location

    Need advice on VBA Conditional formatting Formula : Run-time error '5'

    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:

    error.png

    and the error is :

    error 5.png

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Too many quotes. Try

        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=NOT(A1='" & shtName & "'!A1)"
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •