Home > database >  vba conditional formatting select a rule
vba conditional formatting select a rule

Time:11-11

In an excel you can easily use COnditional FOrmatting> New Rule> under Select a Rule bar you can choose one mode from 6 available. But inside vba you can only use Select cell that contains mode. I need to change it to 6th one, Select custom condition. Is there any possibiliy. Maybe the issue is due to I use percentages?

My code is simple;

With ThisWorkBook.Worksheets(1).Range("A1").FormatConditions.Add(xlCellValue,xlGreaterEqual, "=$B3>=100%")
.AppliesTo=Range("A1")
.StopIfTrue=False
    With .Font
    .ColorIndex=4
    End With
End With

CodePudding user response:

Several issues:

  • It's FormatConditions.Add (probably a typo in your question)
  • xlGreaterThan is not a valid option. Probably you meant xlGreaterEqual. See the docs for a list of options.
  • 100% is equivalent to 1.
Dim cond As FormatCondition
Set cond = ThisWorkbook.ActiveSheet.Range("A3").FormatConditions.Add(xlCellValue, xlGreaterEqual, 1)

EDIT:

If you want to use an expression, then:

With ThisWorkBook.Worksheets(1).Range("A1").FormatConditions.Add(Type:=xlExpression, Formula1:="=$B1>=1")
    .AppliesTo=Range("A1")
    .StopIfTrue=False
     With .Font
        .ColorIndex=4
     End With
End With
  • Related