Q: How to color a cell based on values in two cells

[abcd1234]

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

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

[Nicholas B]

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
>
>

[Ardus Petus]

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

[George King]

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

GeorgeK

"abcd1234" <[email protected]> wrote in
message news:[email protected]...
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>

[Ardus Petus]

Over 3 conditions (see code)

--
AP

"George King" <[email protected]> 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" <[email protected]> wrote
in
> message news:[email protected]...
> 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>
>
>

[abcd1234]

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

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

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[abcd1234]

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

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

[QUOTE=Ardus Petus]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/

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[abcd1234]

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

[Ardus Petus]

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 Format>Conditional
formatting with formula:
=B1<0.05

HTH
--
AP

"abcd1234" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[abcd1234]

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.

[QUOTE=Ardus Petus]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 Format>Conditional formatting with formula:
=B1<0.05

[Ardus Petus]

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]

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

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

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[abcd1234]

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

[QUOTE=Ardus Petus]When more than 1 cells are changed simultaneusly, the macro exited
I have corrected that:

[snip]

[abcd1234]

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

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

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

[Ardus Petus]

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" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Monday, March 13, 2006 12:36 AM
Subject: Re: 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.
>