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.....