How to retrieve a manual selection of Excel in VBA and apply a color code to all cells of the selection

This VBA code below helps to apply a specific color coding formatting to a specific selection without using the conditional formatting of excel.
Note: in this example a user interface is used with the the userform command RefEdit to allow the user to manually select the range of cells he wants to apply the formatting on. 

Private Sub CommandButton1_Click() 'macro to release when the button "Put color" is pressed

Dim rng As Range
Dim c As Range

Set rng = Range(RefEdit1.Value) 'RefEdit1 refers to the textbox refedit of the userform

For Each c In rng 'loop to go through all the cells of the selected range

    On Error Resume Next
    If Application.WorksheetFunction.IsNumber(c.Value) And c.Value <> "" Then 'if the cell content is a number and not empty...
        If c.Value >= 0 Then 'then depending on its value, we apply a fill color
        c.Interior.ColorIndex = 43 'green
        Else
        c.Interior.ColorIndex = 46 'red
        End If
    ElseIf c.Value <> "" Then 'if the cell content is a text
        c.Interior.ColorIndex = 34 'blue
    Else 'if the cell is empty
        c.Interior.ColorIndex = 2
    End If

Next

End Sub

Before Macro (click on "color code macro"):

Userform opening:

Click on the cell selection button (control "refedit") and manual selection.

Click on "Put Color" button to apply specific color coding:

(green for positive values, red for negative values, blue for text, rest in white)

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to change a shape color in Excel using a VBA macro

Change Excel shape interior color and border color using VBA ColorIndex

VBA:

Sub change_shape_color()
     Dim shp As Shape
     With ActiveSheet.Shapes("My_shape").OLEFormat.Object
         Select Case .Interior.ColorIndex
             Case 3
                 .Interior.ColorIndex = 43 'light green
                 .Border.ColorIndex = 10 'dark green
             Case 43
                 .Interior.ColorIndex = 45 'light orange
                 .Border.ColorIndex = 46 'dark orange
             Case Else
                 .Interior.ColorIndex = 3 'light red
                 .Border.ColorIndex = 30 'dark red
         End Select
     End With
 End Sub

Excel:

Assign macro to Excel shape: Right click > Assign_Macro... and select the macro "change_shape_color" written above.

Created by DoMyExcel.com

Customized Excel Solutions starting at $29!

Code syntax: charles.racaud.free.fr