Inserting dynamic arrows

[johnnic]

I want to insert an arrow into a cell that will change its shape and color, ie up or down - green or red, dependant on the relationship between two cells.

For example, if a current share price moves above its purchase price, I want the arrow to be upward and green and if it has moved down I want it to be downward and red.

I'd really appreciate some advice on this one?

 

[northwolves]

Try the following codes:

Sub Macro2()
   If ActiveCell.Value < 1000 Then
   
    ActiveCell = ActiveCell.Value & "↓"
    ActiveCell.Characters(Start:=Len(ActiveCell.Text), Length:=1).Font.Color = vbRed
    Else
    If ActiveCell.Value > 2000 Then
   
    ActiveCell = ActiveCell.Value & "↑"
    ActiveCell.Characters(Start:=Len(ActiveCell.Text), Length:=1).Font.Color = vbGreen
    End If
    End If
End Sub

Best Regards

 

[tusharm]

A simpler way that does not require code nor does it mess with the cell value being numeric.

Suppose your value of interest is in A4, the lower threshold (below which you want to see the red down arrow) is in B4, and the upper threshold (above which you get the green up arrow) is in C4.

Then, in D4 enter the formula =IF(A4< B4,"↓",IF(A4 >C4,"↑","")) where I got the up and down arrows using Insert | Symbol...

Select D4 and add 2 conditional formats (Format | Conditional Formatting...)

Change the first drop down to 'Formula Is'. Enter the formula =$A$4>$C$4. Click the Format... button and select the Green color. Back in the C.F. dialog box, click the Add>> button. For the 2nd C.F. change the drop down to 'Formula Is'. In the text box, enter the formula =$A$4<$B$4. Click the Format... button, and pick the Red color. OK to close all dialog boxes.

Edit: If you want to try a new add-in I put together over the past few days, see
Generating an audio alert
http://www.tushar-mehta.com/publish_train/xl_vba_cases/TM_Audio_Alerts.htm

I want to insert an arrow into a cell that will change its shape and color, ie up or down - green or red, dependant on the relationship between two cells.

For example, if a current share price moves above its purchase price, I want the arrow to be upward and green and if it has moved down I want it to be downward and red.

I'd really appreciate some advice on this one?

 

[johnnic]

thanks, much appreciated.