Automatically changing arrows

[Nelson B.]

I have a report for executives where I have a big "up" arrow if there was an
increase and a big "down" arrow if the amount decreased. Currently I have to
change these by hand each month. How do you automate the arrow to change
based on whether a referenced cell is pos. or neg.?

Thanks!

 

[Gord Dibben]

How have you created the big arrow and where is it located?

I am sure your request could be accommodated with some more detail.

One example.............in an adjacent cell enter this formula.

=IF(H5<0,CHAR(234),IF(H5>0,CHAR(233),CHAR(232)))

Format the cell to Wingdings font.

Neg will show down arrow(234), positive an up arrow(233), no change a
right arrow(232)


Gord Dibben MS Excel MVP

 

[Nelson B.]

I created the arrow simply by inserting it as a shape. I have since changed
the colors and size for both the pos. arrow and the neg. If I have an arrow
inserted in cell b2 and a different one in c2, is it possible to have one
display for a positive result and the other for a negative?

Thanks!

 

[Gord Dibben]

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar Autoshapes>Block
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is > 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

 

[Nelson B.]

Thanks for your help! Much appreciated.

You cannot "insert" an object into a cell..........only lay it on top of a
cell.

Assuming you have two arrows from the Drawing Toolbar Autoshapes>Block
Arrows sitting on your sheet on top of B2 and C2 or wherever you wish.

Right-click on the sheet tab and "View Code". Copy/paste this code into
that module.

You may have to edit the shapes(no.). My up arrow is shape1 and down arrow
is shape2. Select a shape and look in NameBox for shape name/number in
your sheet.

Code assumes a formula in A1 with positive or negative value. No arrow for
0 value. Edit the Case is to give 0 a pos or neg value if you want.

Private Sub Worksheet_Calculate()
Dim shp As Object
On Error GoTo stoppit
Application.EnableEvents = False
For Each shp In Me.Shapes
shp.Visible = False
Next shp
With Me.Range("A1")
Select Case .Value
Case Is > 0: Me.Shapes(1).Visible = msoCTrue
Case Is < 0: Me.Shapes(2).Visible = msoCTrue
End Select
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord

 

[Nelson B.]

Hi again. Is there anyway I could send you an example of what I want? I
can't attach anything to these responses. I'm not sure I follow your last
reply. I tried what you said, but it dumped me into visual basic- something
I'm not familiar with.

 

[Gord Dibben]

Send the workbook to me personally.

Change the AT and DOT in my posting email to send to me.


Gord