Excel macro for row selection

[Umesh Banga]

Hi,

I was wondering if someone can advice on the following:

I have got spreadsheet with retirement calculations. I want when the i
one particular column (name - Contributions); the value goes abov
$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 furthe
cells (after that), only in that particular column.

Thanks in advance

 

[Vacuum Sealed]

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.

 

[Don Guillett]

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.

Right click sheet tab>view code>insert 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.