Hi Colin,

OK - Fantastic. I don't know how you do it. Many many thanks.

the following code handles errors and it is better to understand and
modify:

Sub ReplaceVal2()
Dim strCond As String, FirstAddress As String
Dim arrCond As Variant
Dim LRow As Long
Dim c As Range
Dim SCol As Long, ACol As Long

strCond = Application.InputBox("Please enter the column to search on, "
_
& "the substring, the column to amend, the lower value, the upper
value " _
& " and the value to amend semicolon-separated", "Enter Conditions",
Type:=2)

If strCond = "" Or strCond = "False" Then Exit Sub

arrCond = Split(strCond, ";")
SCol = Asc(UCase(arrCond(0))) - 64
ACol = Asc(UCase(arrCond(2))) - 64
LRow = Cells(Rows.Count, SCol).End(xlUp).Row

Set c = Range(Cells(1, SCol), Cells(LRow, SCol)) _
.Find(arrCond(1), LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Offset(, ACol - SCol) CDbl(arrCond(3)) And _
c.Offset(, ACol - SCol) < CDbl(arrCond(4)) Then
c.Offset(, ACol - SCol) = CDbl(arrCond(5))
c.Offset(, ACol - SCol).Font.Color = vbRed
End If
Set c = Range(Cells(1, SCol), Cells(LRow, SCol)).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional