Hi,

I have input cell - Retirement Age.

I was wondering if it is possible for me to hide the rows based on the input in this cell. For example:

if the value in retirement age cell is 55 then it should hide all filled rows after that row.

At the same time I have graph, it should update the graph (I mean chart) to upto age 55 only.

On 3/05/2012 3:57 PM, Umesh Banga wrote:
Hi,

I was wondering if someone can advice on the following:

I have got spreadsheet with retirement calculations. I want when the in
one particular column (name - Contributions); the value goes above
$25,000 it should do two things:

1) Highlight that row (any color)

2) Should reduce the value back to $25,000 in that cell and all further
cells (after that), only in that particular column.

Thanks in advance.




Hi Umesh

Change Sheet Name, Ranges and Color to suit..


Sub FindValue()

Dim mySht As Worksheet
Dim myRng As Range
Dim c As Range

Set mySht = Sheets("Sheet1")
Set myRng = mySht.Range("A2:A10")

For Each c In myRng
If c 25000 Then
With c
.EntireRow.Interior.Color = 65535 'Yellow
.Value = 25000
End With
End If
Next

End Sub

HTH
Mick.

On Thursday, May 3, 2012 12:57:18 AM UTC-5, Umesh Banga wrote:
Hi,

I was wondering if someone can advice on the following:

I have got spreadsheet with retirement calculations. I want when the in
one particular column (name - Contributions); the value goes above
$25,000 it should do two things:

1) Highlight that row (any color)

2) Should reduce the value back to $25,000 in that cell and all further
cells (after that), only in that particular column.

Thanks in advance.




--
Umesh Banga

Right click sheet tabview codeinsert this. Change column to suit

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Or .Column < 1 Then Exit Sub
If IsNumeric(.Value) And .Value 25000 Then
.Interior.ColorIndex = 6
.Value = 25000
End If
End With
End Sub


Don't see this part???
and all further cells (after that), only in that particular column.