Ardus very generously provided a working solution (refer to the
sub-thread below), that worked for 2 columns of data. My intention is
to apply this to microarray data (multiple columns); accordingly, I was
able to 'tweak' his code very slightly, as indicated below, that works
perfectly!

I don't really understand how the code only colors the cells in Cols.
A, C and E (as desired), but it is working as I want it to - at least
when applied to this limited dataset!

For those of you interested in the sample input/output Excel file, I
have uploaded it to:

http://cjoint.com/?dnayzpZKpc

(I had to WinZIP the file, to get it below the 500K upload size
limit.)

Thank you all once again for your very kind replies!

With best regards, Greg S. :)

====================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Range

If Intersect(Target, Range("A:B")) Is Nothing Then
Exit Sub
End If

For Each oCell In Intersect(Target, Columns("A:F"))

If oCell.Offset(0, 1).Value = 0.05 Then
oCell.Interior.ColorIndex = xlNone
oCell.Font.Bold = False
Else
Select Case oCell.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
Next oCell

End Sub


abcd1234 Wrote:
Hello: I originally posted this in
microsoft.public.excel.worksheet.functions, but have not received any
responses so far ...

I wish to color a column of cells containing numerical values in Column
A, based on the statistical significance (p-values) in Column B. For
example:

-----A---B ---
1 2.00 0.01
2 -5.89 0.004
3 10.05 0.43

If the p-value in Column B is less than 0.05 (p < 0.05), then I want to
color the cell in Column A in the same row (adjacent cell) according to
the code pasted below.

If the p-value is equal to or greater than 0.05 (p = 0.05), then I
want to leave the adjacent cell (Column A) uncolored.

Here is the VBA code, that I copied from the web and 'tweaked' (I have
no experience with Excel VBA code or macro programming, but I
understand the basics of implementing the code).

If somebody could address this question, that would be really
appreciated!

Sincerely, Greg S. :)

------------------------------

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False

Case Is < -10
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case -10 To -5
Cell.Interior.ColorIndex = 46
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case -5 To -0.5
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case 2 To 5
Cell.Interior.ColorIndex = 35
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case 5 To 10
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case 10 To 1000
Cell.Interior.ColorIndex = 10
Cell.Font.Bold = True
Cell.Borders.ColorIndex = 1

Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

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