What would I need to add to change the font color. I'd like font color WHITE
and BLACK. Can I do something like this:

Case Is = 6: iColor = 3: fcolor = 1


"Dave Peterson" wrote:

Nope.

Worksheet_change is an event that fires when someone changes something manually.

You could use the worksheet_Calculate event, though:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("b1:t14").Offset(0, 26)

For Each myCell In myRng.Cells
iColor = -9999
Select Case myCell.Value
Case Is = 6: iColor = 3
Case Is = 5: iColor = 45
Case Is = 4: iColor = 6
Case Is = 3: iColor = 5
Case Is = 2: iColor = 13
Case Is = 1: iColor = 39
End Select
If iColor -9999 Then
myCell.Offset(0, -26).Interior.ColorIndex = iColor
End If
Next myCell
End Sub




Barb Reinhardt wrote:

Will this work if the worksheet changes based upon a calculated value in
AB2:AT14?

"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Long

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then
Exit Sub
End If

iColor = -9999
Select Case Target.Value
Case Is = 6: iColor = 3
Case Is = 5: iColor = 45
Case Is = 4: iColor = 6
Case Is = 3: iColor = 5
Case Is = 2: iColor = 13
Case Is = 1: iColor = 39
End Select

If iColor -9999 Then
Target.Offset(0, -26).Interior.ColorIndex = iColor
End If

End Sub



Barb Reinhardt wrote:

I've found this macro and I wonder what might need to be changed to do what I
want to do:

http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The range I want to format is B1:T14 and the formulas I want to use a

To color cell B2: Formula currently in place is

If AB2 =6, Highlight color = 3
If AB2 = 5, Highlight color = 45
IF AB2 =4, Highlight color = 6
If AB2 =3, Highlight color = 5
IF AB2=2, Highlight color = 13
If AB2 = 1, Highlight color = 39

For C2, I'm checking against AC2.

Can someone assist?

Thanks in advance,
Barb Reinhardt

--

Dave Peterson


--

Dave Peterson