Combining 2 ranges into one VBA Code String

[PGrinstead]

Hi all.

I have a query that I think if quite basic, but as I'm a VBA novice who just tries to bodge other people's code, I'd really appreciate some help.

I have the following code:

    Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oCell  As Range
    Dim rng    As Range
    
    'Range to be highlighted
    Set rng = Range("C5:FJ254")
    
    Application.EnableEvents = False
    
On Error GoTo CleanUp
    
    If Not Intersect(Target, rng) Is Nothing Then
        For Each oCell In rng
            With oCell
            
            'Highlight criteria
                Select Case .Value
                    Case Is = "p"
                        .Interior.Color = RGB(232, 182, 201)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "o"
                        .Interior.Color = RGB(207, 193, 141)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "n"
                        .Interior.Color = RGB(242, 216, 106)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "m"
                        .Interior.Color = RGB(106, 187, 242)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "l"
                        .Interior.Color = RGB(182, 232, 213)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "k"
                        .Interior.Color = RGB(153, 204, 0)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "j"
                        .Interior.Color = RGB(255, 255, 0)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "i"
                        .Interior.Color = RGB(0, 102, 255)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "h"
                        .Interior.Color = RGB(214, 0, 147)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "g"
                        .Interior.Color = RGB(255, 153, 0)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "f"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "e"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "d"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(0, 0, 0)
                    Case Is = "c"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(255, 0, 0)
                    Case Is = "b"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(255, 0, 0)
                    Case Is = "a"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(0, 204, 255)
                    Case Is = "x"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(214, 0, 217)
                    Case Is = "y"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(214, 0, 217)
                    Case Is = "z"
                        .Interior.Color = RGB(255, 255, 255)
                        .Font.Color = RGB(255, 153, 0)
                    Case Else
                        .Interior.ColorIndex = xlNone
                End Select
                
            End With
        Next oCell
    End If
CleanUp:      Application.EnableEvents = True
End Sub

Which works fine.

However, I'm trying to use a similar piece of code on the same worksheet, but on a different range, with difference RGB codes.

So where it says "x" (actual text removed for privacy reasons) in the main body of the table, it goes 153, 204, 0, I want it a different colour in the same worksheet.

I can't seem to combine the two codes together, as all I do is duplicate the above code, and remove the 'End Sub' from the previous code, then alter the range and the RGB codes. It won't run, but I'm not advanced enough in VBA to figure out why.

Your help would be greatly appreciated!!

Thanks in advance.

 

[jmthompson]

Your very first action looks for a change in your specified range before it proceeds with your code.

 If Not Intersect(Target, rng) Is Nothing Then

You will need to do more than tack your code on at the end.

If you want the single macro to perform either the original code or the new code, you can embed the new code as an Else. If you want it to do both codes, alter both ranges at once, you will need to embed with either an Or or And change to the code above.