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

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.


"Miki" wrote:

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

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.


"Miki" wrote:

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

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

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.