Solved: Cell formatting

[blastpwr1970]

Hi,

Could somebody help me,

I need to change a cell base on the numbers entered.

A1

100
150
100
50

100


if any of the numbers in range A1:A10 changes higher that 150 then format H1 to a different color "red" and enter "out of range" in cell H1.

Thank you for all of your help in advance.

[mdmackillop]

Hi Julio
Try this Worksheet sub.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim Rng As Range, Cel As Range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Range("H1")
For Each Cel In Range("A1:A10")
If Cel > 150 Then
.Interior.ColorIndex = 3
.Value = "out of range"
Exit Sub
Else
.Interior.ColorIndex = xlNone
.Value = "OK"
End If
Next
End With
End If
End Sub

[mdmackillop]

Hi Julio,
I amended my code to avoid erroneous correction of the H1 cell when values changed.

[blastpwr1970]

Hi,

I would like to to keep the formating, if any cell still out of range.
"H1" changes, if I update any of the values from "A1:A10" to anything else.

If is not too much to ask.

Thank you

[mdmackillop]

My edited post should do this. I just thought of that after posting.

[blastpwr1970]

You are great it works' perfect.