Conditional Formatting VBA

Regular Contributor

Hello Everyone,

With the help of VBA code,  If any number is smaller than 25, then it come pink. And if  any number is greater than 100 smaller equal than 150, then it come green and If any number is greater than 150 it becomes another color...


So, what should i write in VBA code??


Please help..


Here is a attached file..

3 Replies


File inserted


Public Sub list()
Dim Zelle As Range
For Each Zelle In Range("B1:B14")
  Select Case Zelle.Value
     Case Is <= 25: Zelle.Font.ColorIndex = 26
     Case Is < 150: Zelle.Font.ColorIndex = 4
     Case Is > 100: Zelle.Font.ColorIndex = 4
     Case Else: Zelle.Interior.Font = xlAutomatic
  End Select

End Sub



With your permission, I can't only recommend conditional formatting without VBA for such things, it is more cumbersome but more secure than VBA, as it can lead to conflicts on so many different versions. Here is some information about it ... even though I may be aware that you may not have deliberately preferred this option.

Instruction video available in the link

Use conditional formatting to highlight information

You can download a sample workbook that contains different examples of applying conditional formatting. Download: Conditional formatting examples in Excel


Hope I was able to help you with this info.


I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Thank you for the solution.
Sir, i want to highlight numbers with the help of VBA code


If you want conditional formatting:

Sub Macro1()
    With Range("B1:B14").FormatConditions
        .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=25").Interior.Color = RGB(255, 63, 255)
        .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100").Interior.Color = RGB(0, 255, 153)
        .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=150").Interior.Color = RGB(160, 255, 189)
    End With
End Sub