VBA Conditional Formatting with Gradient Fill

Hi I'm trying to combine two sets of VBA but as I have very little expeience of dealing with VBA I don't know how. Basically I want to end up with what I described in the title.

The First thing I'm wanting is to fill the cells background based on its Value, and based on what i've found online I have this example:

If A1 contains "Emily", A2 contains "Joshua", A3 contains "Steve", and B1 to B3 contain values between 200 and 500 I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Set I = Intersect(Target, Range("B2:F11"))
If Not I Is Nothing Then
Select Case Target
Case "Emily": Newcolor = 37
Case "Joshua": Newcolor = 46
Case "Steve": Newcolor = 12
Case 200 To 300: Newcolor = 10
Case 301 To 400: Newcolor = 3
Case 401 To 500: Newcolor = 25
End Select
Target.Interior.ColorIndex = Newcolor
End If

But I also want the fill to be gradient with up to 3 colours and also be able to change the text colour rather than just having the basic one colour formatting of the previous example, more something utilizing the following perhaps?

Set objColorStop = Sheet6.Cells.Interior.Gradient.ColorStops.Add(0)
objColorStop.Color = RGB(0, 64, 255)

Set objColorStop = Sheet6.Cells.Interior.Gradient.ColorStops.Add(0.33)
objColorStop.Color = RGB(200, 64, 0)

Set objColorStop = Sheet6.Cells.Interior.Gradient.ColorStops.Add(0.66)
objColorStop.Color = RGB(128, 250, 0).

But I have no idea what to edit in or out or how to construct a refined code, Any help in putting this together would be really appreciated!

If you can provide a sample spreadsheet, it would help a lot. However, it doesn't appear that VB would be required to do this. I think it can be accomplished by conditional formatting.