Conditional Formating on Worksheet Change

[Barb Reinhardt]

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-conditional-formatting-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 are:

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

[Mark Driscol]

If you are always going to be changing the colorindex of cells 26
columns to the left of the cell that is changed (e.g., change cell AC2
leads to changing colorindex of cell C2), something like this may work
for you.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

Select Case Target.Value
Case 1
icolor = 39
Case 2
icolor = 13
Case 3
icolor = 5
Case 4
icolor = 6
Case 5
icolor = 45
Case Else
icolor = 3
End Select

Target.Offset(0, -26).Interior.ColorIndex = icolor

End Sub

Mark

[Dave Peterson]

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

--

Dave Peterson

[Barb Reinhardt]

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

[Dave Peterson]

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

--

Dave Peterson

[Barb Reinhardt]

I changed it to this:

Option Explicit

Private Sub Worksheet_Calculate(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

and got the following error:
Procedure declaration does not match description of event or procedure
having the same name.

What do I need to change?

[Dave Peterson]

Oopsie.

Try changing it to this:

Private Sub Worksheet_Calculate()

(I made the changes, compiled and forgot to change the procedure's name!)

But if you're using these kinds of events, you can always use those dropdowns at
the top of the code window to choose the object and event (worksheet and change
in this case).

--

Dave Peterson

[Barb Reinhardt]

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]

Test it out on a small subset of the lines and see what happens!

--

Dave Peterson

[Barb Reinhardt]

I've got it working. Thanks for your assistance.

[Dave Peterson]

Not too difficult, huh? <vbg>

--

Dave Peterson