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

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

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

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

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

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