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 is greater than 150 it becomes another color...

 

So, what should i write in VBA code??

 

Please help..

 

Here is a attached file..

Macros and VBA

3 Replies

  • 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