Percentage difference between numbers in 2 cells

[Rajagopal]

Hi,

I have an excel sheet where I need to mark the rows in red colour if
the value in the current row is + or - 5% the previous row. Please suggest me
How I can do it.

Regards,
Rajagopal

[Peter Rooney]

Rajagopal

You could try pasting this event code into the code window for the worksheet
where you want to do the checking (NOT a normal code module)

Private Sub worksheet_change(ByVal Target As Range)

Dim CheckRange As Range
Dim CheckCell As Range

Set CheckRange = activesheet.Range("CheckRange") 'this could be
range("A1:A15")

For Each CheckCell In CheckRange
If CheckCell.Row > CheckRange.Cells(1).Row Then 'don't check the
first cell in the range
If CheckCell.Value > (CheckCell.Offset(-1, 0).Value * 1.05) Then
CheckCell.Font.ColorIndex = 3
Else
CheckCell.Font.ColorIndex = 1
End If
End If
Next

End Sub

Hope this helps

Pete



"Rajagopal" wrote:

> Hi,
>
> I have an excel sheet where I need to mark the rows in red colour if
> the value in the current row is + or - 5% the previous row. Please suggest me
> How I can do it.
>
> Regards,
> Rajagopal
>
>

[Peter Rooney]

Rajagopal,

Sorry, I only did +5% - I forgot -5% !

Private Sub worksheet_change(ByVal Target As Range)

Dim CheckRange As Range
Dim CheckCell As Range

Set CheckRange = ActiveSheet.Range("CheckRange") 'this could be e.g.
range("A1:A15")

For Each CheckCell In CheckRange
CheckCell.Offset(-1, 0).Address & " " & CheckCell.Offset(-1, 0).Value)
If CheckCell.Row > CheckRange.Cells(1).Row Then 'don't check the
first cell in the range
If CheckCell.Value >= (CheckCell.Offset(-1, 0).Value * 1.05) Or _
CheckCell.Value <= (CheckCell.Offset(-1, 0).Value * 0.95) Then

CheckCell.Font.ColorIndex = 3
Else
CheckCell.Font.ColorIndex = 1
End If
End If
Next

End Sub


"Rajagopal" wrote:

> Hi,
>
> I have an excel sheet where I need to mark the rows in red colour if
> the value in the current row is + or - 5% the previous row. Please suggest me
> How I can do it.
>
> Regards,
> Rajagopal
>
>

[impslayer]

Rajagopal wrote

> > Hi,
> >
> > I have an excel sheet where I need to mark the rows in red colour if
> > the value in the current row is + or - 5% the previous row. Please suggest me
> > How I can do it.
> >
> > Regards,
> > Rajagopal
> >

What about conditional formatting? That would be pretty straight
forward, using something like "=$A1*0,95" as the condition for the
lower bound if you start your values on the first row in the first
column. Just select all the rows you want it to work on (row 2 and
down)...

Depending on exactly what you want you can use 'between' adding a
"=$A1*1,05", or adding the "=$A1*1,05" as an additional condition.
(Using ',' as decimal separator, might be '.' for you.)

/impslayer, aka Birger Johansson

[Peter Rooney]

Hi, Birger,

I see where you're coming from, but I've never been able to use formulae in
conditional formatting - I don't understand how you set the relative formula
to say ("if the cell above is...")

Typical, I can do it in VBA, but normal Excel - something wrong there, I
think..! :-)

Pete



"impslayer" wrote:

>
> Rajagopal wrote
>
> > > Hi,
> > >
> > > I have an excel sheet where I need to mark the rows in red colour if
> > > the value in the current row is + or - 5% the previous row. Please suggest me
> > > How I can do it.
> > >
> > > Regards,
> > > Rajagopal
> > >
>
> What about conditional formatting? That would be pretty straight
> forward, using something like "=$A1*0,95" as the condition for the
> lower bound if you start your values on the first row in the first
> column. Just select all the rows you want it to work on (row 2 and
> down)...
>
> Depending on exactly what you want you can use 'between' adding a
> "=$A1*1,05", or adding the "=$A1*1,05" as an additional condition.
> (Using ',' as decimal separator, might be '.' for you.)
>
> /impslayer, aka Birger Johansson
>
>

[impslayer]

Peter Rooney skrev:

> Hi, Birger,
>
> I see where you're coming from, but I've never been able to use formulae in
> conditional formatting - I don't understand how you set the relative formula
> to say ("if the cell above is...")
>
> Typical, I can do it in VBA, but normal Excel - something wrong there, I
> think..! :-)
>
> Pete
>

Pete,

I actually just did a quick test to see if it worked, and it indeed
seemed to work

Normally the formula is like "=$A$1*0,95", but you can remove the "$"s
to make it a relative reference which seems to work perfectly even when
adding the additional formatting to several cells (starting from A2,
which will make the above (but relative) formula mean 'the row
before')!

Best regards,

/impslayer, aka Birger Johansson