Excellent - Thank you Ardus for your patience, help and prompt replies -
so nice!! ;)
One last question: The code you supplied, below, does what I want
(I had to change
If oCell.Offset(0, 1).Value < 0.05 Then
to
If oCell.Offset(0, 1).Value = 0.05 Then
). However, I need to either enter each value in Column A separately
for the code to be applied to that cell, either by typing a value, or
copying and pasting a single Column A cell. If I select two rows of
values in Column A, cut and re-paste them (for example), the cells do
not get colored (provided the p-value in Column B is < 0.05).
Is it possible to have the all the cells in Column A colored
dynamically, so that when I paste data into Columns A and B, the cell
coloring in Column A updates automatically?
Thanks! Cheers, Greg :)
Ardus Petus Wrote:
Sorry: I did not properly read your original posting.
Here is the code.
Whenever either col A or B changes, it applies the formatting to col
A.
'------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Agrandir la plage verticalement selon les besoins
Dim oCell As Range
If Intersect(Target, Range("A:B")) _
Is Nothing _
Or Target.Count 1 _
Then
Exit Sub
End If
Set oCell = Cells(Target.Row, "A")
If oCell.Offset(0, 1).Value < 0.05 Then
oCell.Interior.ColorIndex = xlNone
oCell.Font.Bold = False
Else
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 If
End Sub
'---------------------------------------
HTH
--
AP
--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread:
http://www.excelforum.com/showthread...hreadid=521381