Conditional coloring of Excel cells, based on adjacent cell values?

G

Greg Stuart

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
___________________________________
 

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

Top