Placing Icons

[Miki]

Hi

I'm facing a problem from last month but I'm not able to put Icons whether
it can be done through conditional formatting or through a macro.

If somebody can help as its really urgent for me !!!!!

I have values for two months - Current and Previous. If current month's
value is closer to zero than previous month's value then i want to show a
green arrow. If current month's value is away from zero than previous
month's value then i want to show a red arrow. Though these values are
positive and negative both, i need to apply conditional formatting on
absolute values.

e.g.

previous Current Arrow Colour
1.2% 2.4% Red Arrow
1.2% -2.4% Red Arrow
-1.2% 2.4% Red Arrow
-1.2% -2.4% Red Arrow

-2.4% 1.2% Green Arrow
-2.4% -1.2% Green Arrow
2.4% -1.2% Green Arrow
2.4% 1.2% Green Arrow

But the conditional formatting will be applied on absolute values and I want
to put the condition in same cell in which the current month values are
shown.

I tried it with conditional formatting but not able to get the desired result.

Any help will be appreciated..

Regards,
Miki

 

[Mike H]

Hi,

To begin with please don't multi post. There's no need you will get an
answer from a single post.

Assuming you 'Current month' data are in column b starting in B2. Select all
that data then

Apply a conditional format of

=ABS(B2)>ABS(A2)
select RED as the fill colour.

Add another condition and enter the formula
=ABS(B2)<ABS(A2)
select GREEN as the fill colour

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

If you want colour coded arrows in column C then you can do this. From the
drawing toolbar select a left pointing block arrow and paste it into C2.
Resize it so it is within confines of a single cell.

Copy the shape and paste it into as many cells as required in column C once
again ensuring each is within the confines of a single cell.

Now right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Myrange As Range
For Each Shape In ActiveSheet.Shapes
Shape.Line.Weight = 0.1
Set Myrange = Range(Shape.TopLeftCell.Address)
If Myrange.Column = 3 Then
If Abs(Myrange.Offset(, -1)) > Abs(Myrange.Offset(, -2)) Then
Shape.Fill.ForeColor.SchemeColor = 10 'red=10
Else
Shape.Fill.ForeColor.SchemeColor = 11 'Green=11
End If
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Wouter HM]

Hi,

For Excel 2007:
Assuming Previous is in Column A and Current is in column B

In column C use the foruma:

=IF(ABS(A2)<ABS(B2),1,3)

For these cells us Contional Formating with 3 colored arrows.
Change the properties so only pictograms will be shown.

HTH,

Wouter

 

[Wouter HM]

Hi,

I made a small change to the formula:

=IF(ABS(A2)<ABS(B2),1,=IF(ABS(A2)>ABS(B2),3,2))

This will show a yellow arrow when Previous and Current have the same
value.

Wouter.