Inserting dynamic arrows

johnnic

Board Regular
Joined
Aug 31, 2006
Messages
127
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
Try the following codes:
Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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?
 

Forum statistics

Threads
1,141,681
Messages
5,707,796
Members
421,528
Latest member
datdude151

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top