CODEWITHSUNDEEP
vbaexcel
Amanda S
Amanda S

Reputation: 103

How to represent a value of difference of two cells in vba?

My goal is to do conditional formatting. For range B2:B21, I need to highlight all values that are not between the value D2-C2 (average minus std dev) and D2+C2 (avg plus std dev). I couldn't get this to work so I recorded it. But instead of hardcoding the value (28, 40) in formula1 and formula2, I want the values of D2-C2 and D2+C2. Any clue how to do this?

Sub Conditionalformatting()

Range("B2:B21").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=28", Formula2:="=40"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

enter image description here

Upvotes: 0

Views: 202

Answers (2)

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=$D$2-$C$2", Formula2:="=$D$2+$C$2"

Complete Macro

Sub ColorMe()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=$D$2-$C$2", Formula2:="=$D$2+$C$2"

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
Selection.FormatConditions(Selection.FormatConditions.Count).StopIfTrue = False

End Sub

Upvotes: 1

Myles
Myles

Reputation: 176

formula1=cells(2,4).value-cells(2,3)
formula2=cells(2,4).value+cells(2,3)

This should do the job.

Upvotes: 1

Related Questions