Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jan 03, 2022

Conditional Formatting VBA

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 i...
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jan 03, 2022

Excel 

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
Next

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.

Nikolino

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.

Excel's avatar
Excel
Iron Contributor
Jan 03, 2022
Thank you for the solution.
Sir, i want to highlight numbers with the help of VBA code
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 03, 2022

    Excel 

    If you want conditional formatting:

    Sub Macro1()
    With Range("B1:B14").FormatConditions
        .Delete
        .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

Resources