Problem with VBA conditional formatting 1

[kiwieur]

Hi,

I needed to be able to have 6 conditional formats and so I found some VBA code on the net to help me achieve this and it works ok, the code is shown below

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
Dim ifont As Integer



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

        Select Case Target

            Case 0

                icolor = 1
                ifont = 2
            
            
            Case 1

                icolor = 33
                ifont = 1

            Case 2

                icolor = 4
                ifont = 1

            Case 3

                icolor = 6
                ifont = 1

            Case 4

                icolor = 46
                ifont = 1

            Case 5

                icolor = 3

            Case 6

                icolor = 13
                ifont = 2


        End Select

        

        Target.Interior.ColorIndex = icolor
        Target.Font.ColorIndex = ifont
    End If



End Sub

However, i do have an issue if a user inserts a new row within the range as i get an

Run-time error '13' :
Type Mismatch

could someone explain to me how I can stop this happening please if at all possible

Regards

Paul

 

[PHV]

Which line of code is highlighted when in debug mode at the time the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiwieur]

Hi PHV,

thanks for the speedy response

the line highlighted in debug mode is

Case 0

Regards

Paul

 

[GlennUK]

You could put this at the beginning of your sub ( after the sub command ):

If Target.Cells.Count > 1 Then Exit Sub

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[kiwieur]

Hi Glenn,

Thank You that works great

Regards

Paul