Automatic conditional formatting for entire row

[Lizard07]

Hello - I have a condtional formatting code that allows me to get around the 3 conditional formattign limit, but it does not automatically update when I copy in new data. It is very manual to retyepe the data into each cell in order to get the cell color to change. Is there anyway to have this update automatically?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("L3:L100")) Is Nothing Then
Select Case Target
Case 0 To 9
icolor = 43
Case 10 To 19
icolor = 39
Case 20 To 29
icolor = 37
Case 30 To 39
icolor = 36
Case 40 To 49
icolor = 40
Case 50 To 59
icolor = 38
Case 60 To 69
icolor = 15
Case Else
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub


Also, is it possible to change the interior color of a set of cells in that row?

Thanks

 

[Andrew Poulsom]

Your code works for me if I paste into the range L3:L100, but only if it's a single cell. In what way isn't it working for you?

 

[Lizard07]

Where are you pasting the code? This is located in only a specific sheets code, its not a macro in the sense that it does not run, its just a conditional formatting work around.

 

[Andrew Poulsom]

I put the code in the module for the worksheet. It runs automatically when any cells are changed. If I change a single cell in the range L3:L100 the cell is formatted. If I change more than one cell at a time in that range I get a runtime error.

 

[Lizard07]

Ok, my issue is I'm copy/pasting values into the worksheet and I want it to automatically update the cell color without me having to type in each one individually

 

[Andrew Poulsom]

As I said before your code is working for me if I copy/paste. Can you give a copy/paste example where it doesn't work?

 

[Lizard07]

How should I do that? I have a file that contains about 15 sheets and I need the formatting to work on all except 1 of them - I don't know if that affects it or not.

Each week, my macro clears the contents of each sheet and then copy/past values the new week's data into each sheet. But the cell colors don't change. Is there an automatic formatting setting I need to change?

How are you copy/pasting?

 

[Andrew Poulsom]

The macro won't fire unless the changed cell(s) are in the range L3:L100. So, for example it won't fire if you paste into A1:L100 or L1:L100.

I just put 42 in a cell and copied it into one of the cells in the range L3:L100.

 

[Lizard07]

Ok yes it does change if you copy and paste one cell at a time, but I need to copy 50 - 100 lines at once. Is that possible?

 

[Andrew Poulsom]

This will work if you change any cell(s) in the range L3:L100, but only if all the cells are in that range:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Dim icolor As Integer
    If Not Intersect(Target, Range("L3:L100")) Is Nothing Then
        For Each Cell In Target
            Select Case Cell.Value
                Case 0 To 9
                icolor = 43
                Case 10 To 19
                icolor = 39
                Case 20 To 29
                icolor = 37
                Case 30 To 39
                icolor = 36
                Case 40 To 49
                icolor = 40
                Case 50 To 59
                icolor = 38
                Case 60 To 69
                icolor = 15
                Case Else
        End Select
        Target.Interior.ColorIndex = icolor
        Next Cell
    End If
End Sub