Changing Excel shapes and color depending on cell value?

Question

How do I change a shape and color of a shape depending on the cell value? Specifically, if the value of a cell is positive, I want to have a green upward pointing arrow and if the value of the cell is negative I want a red downward pointing arrow. I'm using Excel 2007.

Answer 1

I assume you want your arrows in a different column.



Say your values are in col A and your arrows in col B. Click on the letter B and change the font to Wingdings. Now in B1 put

=IF(A1>=0,CHAR(233), IF(A1<0,CHAR(234),""))



Drag this formula down col B as required. Now select col B and on the menu go to Format -> Conditional formatting. Condition 1 ... formula is ... =A1>=0 ... click on the Format button, select green font ... OK. Click the "Add" button. condition 2 ... formula is ... =A1<0 ... click on the format button, select red font ... OK ... OK.



(I'm using excel 2003, 2007 may be slightly different but the same principle).

Answer 2

1

Answer 3

You would have to do that with a macro, or macros. Here is how I would do it.



First, enter 'UP' (no quotes) in cell X1 and hide column X.



Open Excel and show the Drawing toolbar. Click AutoShapes and select 'Block Arrows'. Select the up arrow in row 1 of the Block Arrows and click and drag on onto the worksheet and size and position it as desired. The object name should display as AutoShape 1 just below the Font name in the upper left corner. If it is not AutoShape 1, then make note of what it is named.



Next, if your trigger cell is not A1, then modify the macros below. Replace all "A" references with your cell reference, i.e. "F3", "H2", etc. If your arrow is not named "AutoShape 1" then replace all references in all macros to your shape reference, i.e. "AutoShape 2". Then copy these macros to the clipboard:



'=========

Public tAddr

Sub SetArrow()

tAddr = ActiveCell.Address

If Range("A1").Value > 0 And Range("X1").Value = "UP" Then

Exit Sub

ElseIf Range("A1").Value > 0 And Range("X1").Value = "DOWN" Then

SetGreen

ElseIf Range("A1").Value < 0 And Range("X1").Value = "DOWN" Then

Exit Sub

ElseIf Range("A1").Value < 0 And Range("X1").Value = "UP" Then

SetRed

End If

Range(tAddr).Select

End Sub



Sub SetGreen()

ActiveSheet.Shapes("AutoShape 1").Select

Selection.ShapeRange. IncrementRotation 180#

Selection.ShapeRange. Fill.ForeColor.SchemeColor = 17

Selection.ShapeRange. Fill.Visible = msoTrue

Selection.ShapeRange. Fill.Solid

Range("X1").Value = "UP"

End Sub



Sub SetRed()

ActiveSheet.Shapes("AutoShape 1").Select

Selection.ShapeRange. IncrementRotation 180#

Selection.ShapeRange. Fill.ForeColor.SchemeColor = 10

Selection.ShapeRange. Fill.Visible = msoTrue

Selection.ShapeRange. Fill.Solid

Range("X1").Value = "DOWN"

End Sub





'========



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste all three macros into the module editing area to the right.



Close the VBE and return to Excel.



Copy these macros to the clipboard:



Private Sub Worksheet_Change(ByVal Target As Range)

SetArrow

End Sub



Private Sub Worksheet_Activate()

ActiveSheet.Shapes("AutoShape 1").Visible = True

End Sub



Select the worksheet that your arrow is in and right click the sheet tab.



Select 'View Code'.



Paste the macro into the sheet module editing area to the right.



Close the VBE and return to Excel.



Since the arrow is already facing up, enter a positive number in your trigger cell. The arrow will turn Green. Enter a negative number in your trigger cell and the arrow will turn red and face down. Enter a positive number in your cell and it will turn green and face up.

Answer 4

I don't know if you can change a graphic in a cell, but I do know that you can change the formatting of the cells (like the font style and/or color, and the background color).



Click the cell that you want the formatting to change in, and click on the "Format" menu, and the "Conditional Formatting" sub-menu.



Keep in mind that it's dependent upon the value of the cell that you you're defining the conditional formatting for. But of course the cell can have a formula in it.

Answer 5

Enter this formula in E4 and copy down to E84 =OR(D4="S",D4="H") If you want the row to be coloured if there is something in columns B or C but not in D use this formula instead. =OR(D4="s",D4="h",B4<>"",C4<>"") With this formula, if there is something in the row in either column B or C and nothing in column D of the row the whole row will be coloured. This will make values in column E TRUE or FALSE. The value will be TRUE if the value in column D is either "S" or "H". Click on the Home Tab, Conditional Formatting, New Rule, Use Formula.... Enter this formula in the formula field: =AND(C4:E4) Select the formatting that you want. Click OK Click on Conditional Formatting, Manage Rules. Locate the rule that you just created either in This Worksheet or this Workbook. Click on the rule and click in the Applies To and change the entry so that it reads =$B$4:$E$84 Cells in Column D will remain blank if they don't have an "S" or "H". The row from Column B to D will change to the formatting that you set if an S or H is in column D because an S or H makes the condition that you set in the conditional formatting is TRUE.