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.