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