Excel: Conditional formatting in VB - Problems pasting data 1

[SBuzzT]

Hi All,

I am using some code to do conditional formattting (since I have more that three conditions and that's all condiitonal formatting will allow). The problem is that whenevr I try to paste multiple cells of data, the formatting does not work. Initially I had a problem deleting multiple cells (runtime error), which is why I have the line:

If Target.Count = 1 Then

But now, I need to also be able to paste multiple rows and columns of data and still have the formatting work. Any ideas? Here is the code (BTW, I apply this formatting to several columns, but to save space, I have only included the code for 1 column here):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then

Dim icolor As Integer
    
    If Not Intersect(Target, Range("C7:C24")) Is Nothing Then
        Select Case Target
            Case 0.9795 To 3
                icolor = 45
            Case 0.8895 To 0.9794
                icolor = 4
            Case 0.8495 To 0.8894
                icolor = 27
            Case 0.001 To 0.8494
                icolor = 3
            Case Else
                icolor = 2
        End Select
        Target.Interior.ColorIndex = icolor
    End If

  End If

End Sub

 

[SkipVought]

hi,

since I have more that three conditions and that's all condiitonal formatting will allow

Only true of Excel 2003 and earlier.

Please post VBA questions in forum707.

But now, I need to also be able to paste multiple rows and columns of data and still have the formatting work.

The SHORT answer is to COPY an existing row and Edit > PasteSpecial -- FORMATS on the entire table range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SBuzzT]

Thanks Skip. You're right - using Excel 2003. There is no "paste special" option that seems to work for this though...

 

[SkipVought]

There ABSOLUTELY is!

COPY a row that is CF'd as desired.

Select ALL rows in the table

Right-click in the selection and select PasteSpecial.

In the Paste Special window select the FORMATS option in the PASTE group.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Going back to your first post, proceess each cell in given range, otherwise you catch only first cell:

If Not Intersect(Target, Range("C7:C24")) Is Nothing Then
    For Each c In Intersect(Target, Range("C7:C24")).cells
        Select Case c
        ...
        End Select
        c.Interior.ColorIndex = icolor
    Next c
End If

Note that the code does not fire for formulas changed by cells outside C7:C24.

combo

 

[SBuzzT]

Great, thanks!

 

[JonPeltier]

Since the formatting is static after you apply it, if the values change, the Worksheet_Change event is needed to reapply the formatting. If you insert rows with data, you will have to reapply the formatting (rerun the code) on these new rows. You also will have to keep track of the size of the range, since it may not be just C7:C24 any more.

Jon Peltier

http://peltiertech.com/WordPress/