[Ben.O]

Hey JB,
I couldn't sing up to register at ExcelForum, where I found you account.
I got a problem with my Excel-file / Conditional Format.
I need 5 condititonal Formats. I almost used on of your VBA-codes (which you 'released' at excelforum.com).
It works very good but not the way I need it.

So I hope you can help me.
I got a spread-sheet. F7 is the results from C7 x D7 x E7. L7 is the result from I7 x J7 x K7.
Depending from the result the cell should be change his colour.
If the result in F7/L7 is

>320 = colour of the cell = red
160-320 = color of the cell = orange
70-160 = color of the cell = yellow
20-70 = color of the cell = blue
<20 = color of the cell = green

I still hope you can help me.

With kind regards from Germany,

Ben

[JBeaucaire]

Hi Ben.

GO ADVANCED and use the paperclip icon to post up a desensitized version of your workbook. We'll look at it together.

[Ben.O]

Hey JB,

sorry for not posting my workbook.

Well here it comes.

As you can see, cell F7 and L7 are the results from C7xD7xE7 and
I7xJ7xK7 so fare so good. The problem is that the cell doesn't change his color. When I wirte manual the result from C7xD7xE7 / I7xJ7xK7 in cell F7/L7 the colour of the cell is changing.

In case that my file doesn't work here is my code I use.

__________________________________________________ ________

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
If Not Intersect(cell, Range("F5:F1000,L5:L1000")) Is Nothing Then
Select Case cell.Value
Case 0 To 0, 1
cell.Offset(0).Interior.ColorIndex = 2
Case 0, 2 To 20
cell.Offset(0).Interior.ColorIndex = 4
Case 20, 5 To 70
cell.Offset(0).Interior.ColorIndex = 5
Case 70, 5 To 160
cell.Offset(0).Interior.ColorIndex = 6
Case 160, 5 To 320
cell.Offset(0).Interior.ColorIndex = 46
Case 320, 5 To 1000
cell.Offset(0).Interior.ColorIndex = 3
End Select
End If
Next cell

__________________________________________________ ________

I hope you can give me the answer to my problem.

Take care,

Ben

[JBeaucaire]

The intersect method only works to evaluate the exact cell that was changed by a user, you are trying to evaluate the numbers in cells that are changing by formulas.

You can still use the WS_CHANGE event, but you'll have to evaluate the entire range of possible cells each time any value changes anywhere on the sheet. That could cause a noticeable lag,

I've changed your macro to work with any change, but I've also adjusted the range of cells being evaluated so it only looks at the cells with actual formulas in them. This should keep it peppy.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Range("F5:F1000,L5:L1000").SpecialCells(xlFormulas, xlNumbers)
    Select Case cell.Value
        Case 0 To 0, 1
            cell.Offset(0).Interior.ColorIndex = 2
        Case 0, 2 To 20
            cell.Offset(0).Interior.ColorIndex = 4
        Case 20, 5 To 70
            cell.Offset(0).Interior.ColorIndex = 5
        Case 70, 5 To 160
            cell.Offset(0).Interior.ColorIndex = 6
        Case 160, 5 To 320
            cell.Offset(0).Interior.ColorIndex = 46
        Case 320, 5 To 1000
            cell.Offset(0).Interior.ColorIndex = 3
    End Select
Next cell

End Sub

[Ben.O]

Hey JB,

Thank very very very much.
That's exactly what I was looking for.

Take care,

Ben