Results 1 to 9 of 9

Insert new row creates Run-time error 13 with exisiting simple code

Threaded View

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Insert new row creates Run-time error 13 with exisiting simple code

    Found code elsewhere, and tweaked it, that allows me to do some simple conditional formatting to alter the colour of a cell in column G depending on the RAG status entry inserted into a cell (Red,Amber,Green,Complete,N/A). The cells in column G have a drop down validation list.

    I'm sure that there is a more elegant way of doing this, but it works for me at the moment. However when I insert a row I get an Run-time error '13': Type mismatch message, flagging the
    Case "Red"
    line of code.

    I've tried various things but can't figure it out, but can't get to the bottom of it.

    The code is attached below

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer

    If Not Intersect(Target, Range("G:G")) Is Nothing Then

    Select Case Target

    Case "Red"
    icolor = 3
    Case "red"
    icolor = 3
    Case "Amber"
    icolor = 45
    Case "amber"
    icolor = 45
    Case "Green"
    icolor = 4
    Case "green"
    icolor = 4
    Case "Complete"
    icolor = 32
    Case "complete"
    icolor = 32
    Case "N/A"
    icolor = 15
    Case "Not Applicable"
    icolor = 15
    Case "n/a"
    icolor = 15
    Case Else

    'Whatever

    End Select
    Target.Interior.ColorIndex = icolor
    End If
    End Sub
    Workbook attached also.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1