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

A

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
 
G

Guest

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
 
A

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
 
G

George King

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

GeorgeK

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.
Click to expand...
snip>
 
A

Ardus Petus

Over 3 conditions (see code)

--
AP

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

GeorgeK

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.
Click to expand...
http://www.excelforum.com/showthread.php?threadid=521381
snip>
Click to expand...
 
A

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

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
 
A

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
 
A

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

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]...
 
A

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

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
 
A

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

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
 
A

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

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

[snip]
Click to expand...
 
A

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP W/ VBA: SELECT RANGE, ALLCAPS, CELL COLOR, RETURN TO BLANK CELL/PATTERN CELL 5
VBA limit from sheet to several ranges 2
CODE given: Please help to rectify the error 5
Worksheet Change event on different ranges with data validation 2
Clear Cell Contents based on Criteria of another cell 10
"Error 1004 Unable to set the ColorIndex property of the Interior. 3
Changing cell color based on its value 9
Faster Cycle through Cells 5

Top