Using VBA to change the color of the active cell if the value in thatcell is less than 15%

[Pelham]

Dear All

I am trying to simply create a VBA script to change the color of the
active cell if the resulting value in that cell is less than 15%. In
that cell there is a simple division formula referring to two other
cells to give a percentage in that cell. I have come to the conclusion
that a Function is better than a Sub but the following VBA script
gives me a 'Value!' error. Any ideas for a rookie, please?

Function EfficiencyRatio()
'
' EfficiencyRatio Macro
' Highlights the cell green if the expense ratio is less than 15%
'
' Keyboard Shortcut: Ctrl+Shift+X
'

Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, _
Formula1:="=0.15"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Function

 

[Corey]

Use Conditional Formatting under the Format Tab.
Easier...


Corey....
Dear All

I am trying to simply create a VBA script to change the color of the
active cell if the resulting value in that cell is less than 15%. In
that cell there is a simple division formula referring to two other
cells to give a percentage in that cell. I have come to the conclusion
that a Function is better than a Sub but the following VBA script
gives me a 'Value!' error. Any ideas for a rookie, please?

Function EfficiencyRatio()
'
' EfficiencyRatio Macro
' Highlights the cell green if the expense ratio is less than 15%
'
' Keyboard Shortcut: Ctrl+Shift+X
'

Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, _
Formula1:="=0.15"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Function

 

[Mark Ivey]

See if this fits...


Sub test()
EfficiencyRatio
End Sub

Function EfficiencyRatio()
If activecell.Value < 0.15 Then
activecell.Interior.Color = 13561798
activecell.Font.Color = -16752384
End If
End Function


Mark Ivey

 

[Pelham]

See if this fits...

Sub test()
    EfficiencyRatio
End Sub

Function EfficiencyRatio()
    If activecell.Value < 0.15 Then
        activecell.Interior.Color = 13561798
        activecell.Font.Color = -16752384
    End If
End Function

Mark Ivey










- Show quoted text -

Thank you Corey and Mark!

Yes, I am aware that the conditional formatting is easier but I am
curious to know how I can create my own VBA to do this so that it runs
automatically whenver I open up a new spreadsheet - instead of having
to set the condition each time...

Mark's suggestion gives me an error message in a cell containing the
formula =1-(B5/B4) which I am trying to change to =EfficiencyRatio(1-
(B5/B4)) so that the color changes if it is less than 15%.