Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Greg Stuart
 
Posts: n/a
Default Conditional coloring of Excel cells, based on adjacent cell values?

Hello: I wish to color a column of cells containing numerical values,
based on significance (p-values) in a second column.

If the p-value is less than 0.05 (p < 0.05), then I want to color the
adjacent cell (in the first column) 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 (first column) uncolored.

Here is the VBA code, that I copied from the web and pasted into my
worksheet (rght-clicking the small tab at the bottom, and selecting
the "View Code" selection.

Thank you in advance for your help, 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 "Tom", "Joe", "Paul"
Cell.Interior.ColorIndex = 26
Cell.Font.Bold = True
Case "Mito"
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Cell.Font.ColorIndex = 3

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
___________________________________


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional cell background formatting, based on cell content nosivad Excel Discussion (Misc queries) 5 February 12th 06 12:12 AM
Want to make conditional sounds based on cell values in Excel [email protected] New Users to Excel 1 January 25th 06 04:51 PM
How do I automatically fill a cell based on another cell in Excel SouthCarolina Excel Discussion (Misc queries) 3 January 13th 06 01:52 AM
Pasting Word table cell with paragraph markers into single Excel c Steve Excel Discussion (Misc queries) 1 June 16th 05 11:26 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM


All times are GMT +1. The time now is 09:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"