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

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.FormatConditi ons.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

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

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


Corey....
"Pelham" wrote in message
...
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.FormatConditi ons.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

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

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" wrote in message
...
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.FormatConditi ons.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

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

On Feb 7, 11:06*am, "Mark Ivey" wrote:
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" wrote in message

...



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.FormatConditi ons.Count).SetFirstPriori*ty
* *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- Hide quoted text -

- 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%.