VBA for Excel 2007 tutorial-three-color conditional format


Three-color conditional format

Three-color scale rule applies three colors to a specified range of cells. The min value of range will be set to a color value, mid value set to a color value and the max value set to other color value. In this example, you will learn how to conditionally format a range of cells form A1 to A20 with three-color scale rule.


VBA for Excel: VBA example code to conditionally format cells with three-color scale rule

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.



VBA code for this example:



Private Sub Worksheet_Activate()
    ConFormat
End Sub
 
Sub ConFormat()
 
    Dim fc As ColorScale
    Dim i As Integer
    'Fill cells with sample data from 1 to 20
    For i = 1 To 20
        Range("A" & i) = i
    Next
    'Select the range of cells to apply color scale conditional format
    Range("A1:A20").Select
   
    'Create a Three-color ColorScale object
    Set cf = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)
   
    'Change default colors-min value to red mid value to green, and max value to blue
    cf.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
    cf.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 255, 0)
    cf.ColorScaleCriteria(3).FormatColor.Color = RGB(0, 0, 255)
      
End Sub

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.