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