VBA AutoShape Color Change Script for Formula Cell

**Sean Ross** · 05-29-2013, 09:06 AM

Hi, hope you guys can help - VBA virgin here...

I'm employing the script below to change the fill color of 'FreeForm 9' autoshape based on the value in cell 'U117'.
I understand that if cell 'U117' has a formula in it rather than a simple value, VBA will not use the resulting value.
How can I replace the "U117" reference in the script and use the resulting value from a formula subtracting A1 - A2 instead?
Thanks for any advice

.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("U117")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value > 0.4 Then
ActiveSheet.Shapes("FreeForm 9").Fill.ForeColor.RGB = vbRed
ElseIf Target.Value <= 0.4 And Target.Value > 0.05 Then
ActiveSheet.Shapes("FreeForm 9").Fill.ForeColor.RGB = vbYellow
ElseIf Target.Value <= 0.05 Then
ActiveSheet.Shapes("FreeForm 9").Fill.ForeColor.RGB = vbGreen
End If
End If
End If
End Sub

**ragulduy** · 05-29-2013, 09:34 AM

VBA will pick up the value by default in a term such as:

Please Login or Register  to view this content.

The intersect function determines whether the ranges overlap, and the arguements for the function must be ranges. Therefore you cannot have the resulting formula of A2 - A1 instead of range("U117").

If you want to determine whether the value of "target" is equal to the value of A2-A1 you need:

Please Login or Register  to view this content.

**MickG** · 05-29-2013, 09:35 AM

This worked for me
NB:- If you assume the results of formula cell is "A3" and the "Precedents" cells are "A1 & A2",
Then "A1 :A2" will be the Actual target cells, Not "A3"
The result of formula cell (in you case "U117" ,in Example case "A3") is not the Target cell
(NB:- Target cell= Cell selected)
But by setting the Cell ("U117 or "A3") as a range object "Tar" it can be used in the code as below.
Example code below based on your Code, but with alterations:-

Please Login or Register  to view this content.

**Sean Ross** · 05-29-2013, 11:43 AM

Excellent.
Thanks for the advice guys. All working now, and I've learnt something new.
Cheers

**Sean Ross** · 05-29-2013, 12:16 PM

If in the future I want to duplicate this script to act on additional different cells (and their corresponding freeforms) within the same worksheet, is there a VBA procedure I need to separate them?
(Sorry, very new to this)