Excel Apply Conditional Formatting in VB.NET

Alice H
12y
33.2k
0
0

Article

Introduction

Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells. You can also use VBA to create instances of FormatCondition objects with conditional formatting and add these to the FormatConditions collection.

Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations. Use caution when applying to to large ranges. I have managed to get X to Z working, courtesy of code that someone else produced and I have tweaked, but am finding it difficult to add another range.

Example

Assume that you have a table which contain names and point of the authors of the c-sharpcorner.com like the below figure and we are going to implement conditional formatting on the table:

Now write the following code in visual basic for application code window:

    Private Sub Formatting()
        Dim i, count As Integer
        For i = 1 To 10

            If Cells(i, 2).Value > 10000 Then
                count = count + 1
                Cells(i, 2).Font.ColorIndex = 3
                Cells(i, 3).Value = "Platinum Member"

            ElseIf Cells(i, 2).Value > 5000 And Cells(i, 2).Value < 10000 Then
                Cells(i, 2).Font.ColorIndex = 46
                Cells(i, 3).Value = "Gold Member"

            ElseIf Cells(i, 2).Value > 500 And Cells(i, 2).Value < 5000 Then
                Cells(i, 2).Font.ColorIndex = 48
                Cells(i, 3).Value = "Silver Member"

            Else
                Cells(i, 2).Font.ColorIndex = 53
                Cells(i, 3).Value = "Bronze Member"

            End If
        Next i
    End Sub

Output

In above figure you see we implemented four types of conational formatting, platinum member message and a different color for those author who have more than 10000 points, gold member message and a different color for those author who have less than 10000 points and more than 5000 points, silver member message and a different color for those author who have less than 5000 points and more than 500 points and the last condition bronze member message and a different color for those author who have less than 500 points.

Now next we implement some different king of formatting on the same table which we use in above example. In above formatting you see we show a message and change the color of point on behalf of condition, in this example you see, we change the color of complete row of related condition:

Write the following code in visual basic for application code window:

Private Sub Formatting()
        Dim i, count As Integer
        For i = 1 To 10

            If Cells(i, 2).Value > 10000 Then
                count = count + 1
                Cells(i, 2).Interior.ColorIndex = 3
                Cells(i, 3).Interior.ColorIndex = 3
                Cells(i, 3).Value = "Platimun Member"

            ElseIf Cells(i, 2).Value > 5000 And Cells(i, 2).Value < 10000 Then
                Cells(i, 2).Interior.ColorIndex = 6
                Cells(i, 3).Interior.ColorIndex = 6
                Cells(i, 3).Value = "Gold Member"

            ElseIf Cells(i, 2).Value > 500 And Cells(i, 2).Value < 5000 Then
                Cells(i, 2).Interior.ColorIndex = 48
                Cells(i, 3).Interior.ColorIndex = 48
                Cells(i, 3).Value = "Silver Member"

            Else
                Cells(i, 2).Interior.ColorIndex = 53
                Cells(i, 3).Interior.ColorIndex = 53
                Cells(i, 3).Value = "Bronze Member"

            End If
        Next i
    End Sub

Output

Thank You.....