Thank you Ardus ... I see where you're going, but the code that you
generously supplied doesn't seem to work for me, claiming a bug at the
Set oCell = Target.Offset(0, -1) ' Column A
step.
Also, I don't see where the significance value (p-value < 0.05) is
being entered.
Here is a sample list of columized data (I tried to upload a sample
Book1.xls file contaiing the above data plus the VBA code, but I got an
upload error - invalid file type):
-11.00 0.049
-10.00 0.049
-9.00 0.049
-6.00 0.049
-5.00 0.049
-4.00 0.049
-0.60 0.049
-0.50 0.049
-0.40 0.049
0.00 0.049
0.50 0.049
1.00 0.049
1.90 0.049
2.00 0.049
2.10 0.049
4.90 0.049
5.00 0.049
5.10 0.049
9.00 0.049
9.90 0.049
9.00 0.049
10.00 0.049
10.10 0.049
-11.00 0.050
-10.00 0.050
-9.00 0.050
-6.00 0.050
-5.00 0.050
-4.00 0.050
-0.60 0.050
-0.50 0.050
-0.40 0.050
0.00 0.050
0.50 0.050
1.00 0.050
1.90 0.050
2.00 0.050
2.10 0.050
4.90 0.050
5.00 0.050
5.10 0.050
9.00 0.050
9.00 0.050
9.00 0.050
10.00 0.050
10.10 0.050
-11.00 0.051
-10.00 0.051
-9.00 0.051
-6.00 0.051
-5.00 0.051
-4.00 0.051
-0.60 0.051
-0.50 0.051
-0.40 0.051
0.00 0.051
0.50 0.051
1.00 0.051
1.90 0.051
2.00 0.051
2.10 0.051
4.90 0.051
5.00 0.051
5.10 0.051
9.00 0.051
9.00 0.051
9.00 0.051
10.00 0.051
10.10 0.051
Thanks once again for your help - this is really great! Cheers, Greg
:)
Ardus Petus Wrote:
Try the following and check X & Y values in lines Case X to Y
HTH
--
AP
'---------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Agrandir la plage verticalement selon les besoins
Const myRangeAddr As String = "B:B"
Dim oCell As Range
If Intersect(Target, Range(myRangeAddr)) _
Is Nothing _
Or Target.Count 1 _
Then
Exit Sub
End If
Set oCell = Target.Offset(0, -1) ' Column A
Select Case Target.Value
Case vbNullString
oCell.Interior.ColorIndex = xlNone
oCell.Font.Bold = False
Case Is < -10
oCell.Interior.ColorIndex = 3
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case -10 To -5
oCell.Interior.ColorIndex = 46
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case -5 To -0.5
oCell.Interior.ColorIndex = 44
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case 2 To 5
oCell.Interior.ColorIndex = 35
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case 5 To 10
oCell.Interior.ColorIndex = 4
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case 10 To 1000
oCell.Interior.ColorIndex = 10
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1
Case Else
oCell.Interior.ColorIndex = xlNone
oCell.Font.Bold = False
End Select
End Sub
--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread:
http://www.excelforum.com/showthread...hreadid=521381