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

Greg

Looked at code seems ok to me but
It is event driven code that will not work in isolation
You have to setup Excel for fire these events ... if you are not familiar
with the event model and how it works, you can copy the code into click event
of a button and have it run from there
or copy it into a standard Macro and run the Macro.

OK ??

Hope this helps

Nick

"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

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

Is there some reason you are not using Excel's conditional formatting to do
this?

GeorgeK

"abcd1234" wrote in
message ...
snip
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.


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381
snip

Over 3 conditions (see code)

--
AP

"George King" a écrit dans le message de
news:DTEQf.1081$o41.539@trnddc06...
Is there some reason you are not using Excel's conditional formatting to
do
this?

GeorgeK

"abcd1234" wrote
in
message ...
snip
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.


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread:
http://www.excelforum.com/showthread...hreadid=521381
snip

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

Are your data in columns A and B?

Did you change the constant in:
Const myRangeAddr As String = "B:B"

The offending line should not produce an error.

You can transmit your worksheet via following link (in french):
http://cjoint.com/

HTH
--
AP

"abcd1234" a écrit
dans le message de
...

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

Here is a copy (sample) of my Excel spreadsheet:

http://cjoint.com/data/dmoBcfe6Rx.htm

Ardus Petus Wrote:
Are your data in columns A and B?

Did you change the constant in:
Const myRangeAddr As String = "B:B"

The offending line should not produce an error.

You can transmit your worksheet via following link (in french):
http://cjoint.com/


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile: http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

Leave the line : Const myRangeAddr As String = "B:B" unchanged, since it
specifies the area (column B) upon which Excel should monitor changes.

Within the Select Case block, you should add a paragraph specifying what to
do when B column's value lays between '0.5 and 2:

Case -0.5 To 2
oCell.Interior.ColorIndex = xx
oCell.Font.Bold = True
oCell.Borders.ColorIndex = 1

HTH
--
AP

"abcd1234" a écrit
dans le message de
...

Here is a copy (sample) of my Excel spreadsheet:

http://cjoint.com/data/dmoBcfe6Rx.htm

Ardus Petus Wrote:
Are your data in columns A and B?

Did you change the constant in:
Const myRangeAddr As String = "B:B"

The offending line should not produce an error.

You can transmit your worksheet via following link (in french):
http://cjoint.com/


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

Hello again: I appreciate your assistance, but you do not understand
what I am intending to accomplish - please refer to my original post:

Based on the value in column B, I want to decide whether or not to
color the cell in Column A, accodring to the coloring criteria that I
have specified.

Specifically, if the value in Column B is < 0.05, then I want the
coloring criteria to be applied.

If the value in Column B is 0.05 or greater, then I do not want the
cell in Column A to be color-coded.

I have uploaded a new worksheet, that should illustrate this more
clearly:

http://cjoint.com/?dmqpbNNjnA

Thanks ... Greg :)


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile: http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

According to the code in your original posting, I thought you had several
conditions to test, and several colours to apply according to B's value.

If you have only one condition (B < 0.05), you can use FormatConditional
formatting with formula:
=B1<0.05

HTH
--
AP

"abcd1234" a écrit
dans le message de
...

Hello again: I appreciate your assistance, but you do not understand
what I am intending to accomplish - please refer to my original post:

Based on the value in column B, I want to decide whether or not to
color the cell in Column A, accodring to the coloring criteria that I
have specified.

Specifically, if the value in Column B is < 0.05, then I want the
coloring criteria to be applied.

If the value in Column B is 0.05 or greater, then I do not want the
cell in Column A to be color-coded.

I have uploaded a new worksheet, that should illustrate this more
clearly:

http://cjoint.com/?dmqpbNNjnA

Thanks ... Greg :)


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile:
http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

No ... that is not correct. I want the cells in Column A to be colored
based on their value - a particular color, depending on the range -
ONLY if the associated p-value in Column B is < 0.05.

Ardus Petus Wrote:
According to the code in your original posting, I thought you had
several conditions to test, and several colours to apply according to
B's value.

If you have only one condition (B < 0.05), you can use
FormatConditional formatting with formula:
=B1<0.05


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile: http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

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

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

When more than 1 cells are changed simultaneusly, the macro exited
I have corrected that:

'-------------------------------------
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"))

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" a écrit
dans le message de
...

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

Wicked. You have saved me considerable time, as I want to apply this to
a large spreadsheet of microarray (genetics) data!

I may want to color selected columns (more than one), similar to this
sample ... I'll play around with it at work, and start a new subthread,
if needed.

Thank you once again! Sincerely, Greg :)

Ardus Petus Wrote:
When more than 1 cells are changed simultaneusly, the macro exited
I have corrected that:

[snip]


--
abcd1234
------------------------------------------------------------------------
abcd1234's Profile: http://www.excelforum.com/member.php...o&userid=32376
View this thread: http://www.excelforum.com/showthread...hreadid=521381

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

Here is the correct code for multiple columns:

HTH
--
AP

'------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Range

'Range ("A:F") specifies the cells you want to monitor changes in
If Intersect(Target, Range("A:F")) Is Nothing Then
Exit Sub
End If


For Each oCell In Intersect( _
Target, _
Union(Columns("A"), Columns("C"), Columns("E")) _
)

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
'---------------------------------------------------
----- Original Message -----
From: "abcd1234"
Newsgroups: microsoft.public.excel.programming
Sent: Monday, March 13, 2006 12:36 AM
Subject: Q: How to color a cell based on values in two cells



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. :)