Excel VBA conditional formating with more than 3 conditions

conditional formating in Excel on the basis of more than 3 conditions
How to change the font color and weight and shading of the first [X] cells in all rows in a given excel sheet? Most of the times the conditional formatting option in Excel is enough, but not when you have more than 3 conditions. Here is a simple piece of VB code to help you make it possible to use as many conditions as you can think of. It gives you the ability to change the font color, the background color and the font weight (bold or not) based on all the cases (conditions) you define. In this case it the formatting is changed when the first collumn (A:A) cantains an A, B, C, D, E or F.

 

Private Sub Worksheet_Change(ByVal Target As Range)
'fc = font color
'bc = background color
'bf = bold or not
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
'set d if Target is intersecting with column A
Set d = Intersect(Range("A:A"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
    'c = the value of the cell that just changed value
    'MsgBox UCase(c)
    Select Case UCase(c)
        Case "A"
            fc = 5: fb = True: bc = 10
        Case "B"
            fc = 6: fb = True: bc = 9
        Case "C"
            fc = 7: fb = True: bc = 8
        Case "D"
            fc = 8: fb = True: bc = 7
        Case "E"
            fc = 9: fb = True: bc = 6
        Case "F"
            fc = 10: fb = True: bc = 5
        Case Else
            fc = 1: fb = False: bc = xlNone
    End Select
'here 20 is the number of cells you want to change the format for each row

    With Cells(c.Row, 1).Resize(, 20)
'for each cell in the row change the font color, font weight and background color
        .Font.ColorIndex = fc
        .Font.Bold = fb
        .Interior.ColorIndex = bc
    End With
Next
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *