Excel conditional fomat using VB help 2

[p8md]

I've read past threads and you guys have been a big help. From a previous thread I have added the following code to format the cell for a specific temperature.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("A2:s17")
Select Case oCell.Value
Case 91 To 130
oCell.Interior.ColorIndex = 3
Case 86 To 90.9999
oCell.Interior.ColorIndex = 46
Case 81 To 85.9999
oCell.Interior.ColorIndex = 44
Case 76 To 80.9999
oCell.Interior.ColorIndex = 36
Case 71 To 75.9999
oCell.Interior.ColorIndex = 35
Case 60 To 70.9999
oCell.Interior.ColorIndex = 10
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

It works great if I’m typing data to the worksheet, but the worksheet is a summary page and the data is a reference to another worksheet (one cell would equal “=data!A3”). If the “data” sheet changes I can’t get the “summary” sheet to update the conditional format with the code above. Can I add more code, or add a refresh button? Please help.

Thanks,
Mark

 

[PHV]

You may move the code to the Worksheet_SelectionChange or even the Worksheet_Activate event procedure ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mintjulep]

Move the procedure to the worksheet that is actually changing. Explicitly reference the summary sheet in the range.

Also, clean up your select ... case.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("summary!A2:s17")
        Select Case oCell.Value
            Case 91 To 130
                 oCell.Interior.ColorIndex = 3
            Case Is >= 86
                 oCell.Interior.ColorIndex = 46
            Case Is >= 81 
                 oCell.Interior.ColorIndex = 44
            Case Is >= 76
                 oCell.Interior.ColorIndex = 36
            Case Is >= 71
                 oCell.Interior.ColorIndex = 35
            Case Is >= 60 
                 oCell.Interior.ColorIndex = 10
            Case Else
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

 

[p8md]

Thanks for the cleanup!

OK… I admit… I know enough VB to make me REALLY dangerous.

I think what you were telling me is to move the above code from the color page (Color tab) to the worksheet where the data is changing (Summary tab), correct? I did that and now I get a “Run-Time error ‘1004’: Method ‘Range’ of object’ _Worksheet’ failed” every time I change a value in the data worksheet (Summary tab). When I hit debug, it highlights the "For Each oCell..." line.

What gives?

Thanks,
Mark

My code that I move to the data page:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("Color!A2:S17")
Select Case oCell.Value
Case 91 To 130
oCell.Interior.ColorIndex = 3
Case Is >= 86
oCell.Interior.ColorIndex = 46
Case Is >= 81
oCell.Interior.ColorIndex = 44
Case Is >= 76
oCell.Interior.ColorIndex = 36
Case Is >= 71
oCell.Interior.ColorIndex = 35
Case Is >= 60
oCell.Interior.ColorIndex = 10
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub

 

[PHV]

For Each oCell In Sheets("Color").Range("A2:S17")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[p8md]

Thanks! That works great!