worksheet_activate for Conditional Formatting

[Hanz77]

Following is the code I regularly use when I need Conditional Formatting with more than four conditions. This code works great when you are typing in new data over the old, but I now need to base the formatting on data found in another sheet. For example, in Sheet1 and cell B2 I have a formula that reads =AVERAGE('Sheet2'!$B2:$D40), with this code the conditional formatting in cell B2 does not update when changes occur in Sheet2. To make this work, I thought I could modify the Event Procedure Code to run when Sheet1 is activated. This sounds like it should be easy and a common issue, but I have made several attempts in tweaking this code and searched several forums to ideas, but no luck thus far. Any thoughts are appreciated.

Code:

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

If Not Intersect(Target, Range("B2:D8")) Is Nothing Then
Select Case Target
Case 4.5 To 5
CellColor = 10
Case 4 To 4.4
CellColor = 36
Case 3 To 3.9
CellColor = 45
Case 2 To 2.9
CellColor = 3
Case 1 To 1.9
CellColor = 13
End Select
Target.Interior.ColorIndex = CellColor
End If
End Sub

 

[RoryA]

Neither the worksheet_activate nor worksheet_calculate events provde you with a Target parameter so you would need to change the code completely to look at every cell in the range you are interested in formatting, regardless of whether they ahve actually changed or not. This is not particularly efficient but there is not a lot of choice unfortunately.

 

[Hanz77]

VB - Conditional Formatting based on Cases

Following is a modified version of my origional code. This is an attempt to base the interior color of a range of cells that are based on formulas found in another sheet. This seems like a really straight forward and simple code, but I'm unable to figure out why it is not working. Any thoughts are appreciated.

Code:

Private Sub Worksheet_Activate()

Select Case Range("B2").Activate
Case 4.5 To 5
ActiveCell.Interior.ColorIndex = 10
Case 4 To 4.4
ActiveCell.Interior.ColorIndex = 36
Case 3 To 3.9
ActiveCell.Interior.ColorIndex = 45
Case 2 To 2.9
ActiveCell.Interior.ColorIndex = 3
Case 1 To 1.9
ActiveCell.Interior.ColorIndex = 13
End Select

 

[RoryA]

Re: VB - Conditional Formatting based on Cases

It should be:

Select Case Range("B2").Value

not:

Select Case Range("B2").Activate