Select specif range on Conditional Format

[robotlust]

Hi,
I've managed to conditionally format a cell with VBA (im a vba noobie)
What I really want to do is format a range of rows.
Currently criteria from Column I and I want to color rows E to J.. I’m just not sure how to select the row. Is it Range.Select(), or Cell.Select() ?

I've commented my poor attempt in the code to select the appropriate rows.

Private Sub Worksheet_Calculate()
    Dim icolor As Integer
    For Each r In Range("I10:I30")
            With r
                Select Case .Value
                    Case 0 To 0.69
                       'cells("E:J")
                        icolor = 10  'green
                    
                    Case 0.7 To 0.89
                        icolor = 44  'amber
                        'Range.Select ("E:J")

Cheers
Cam

[rylo]

Cam

Not sure I follow. If I10 is .5, do you want E10:H10 to be colored green?

Is that what you want to do?

rylo

[robotlust]

Hey Rylo,
Absolutley correct!

on another note when i enter a date somewhere i get a Compile Error on the code. It's slecting the first case (Case 0.1 To 0.69) in Yellow highlight. not sure whats happening????

Complete Code:

[rylo]

Hi

Try

Private Sub Worksheet_Calculate()
    Dim icolor As Integer
    For Each r In Range("I10:I30")
            icolor = xlNone
            With r
                Select Case .Value
                    Case 0.1 To 0.69
                        icolor = 10  'green
                    Case 0.7 To 0.89
                        icolor = 44  'amber
                        'Range.Select ("E:J")
                    Case 0.9 To 0.99
                        icolor = 9  'red
                        'Range.Select ("E:J")
                    Case 1 To 1.01
                        icolor = 2  'white
                        'Range.Select ("E:J")
                    Case 1.02 To 99
                        icolor = 3  'red
                    Case Else
                        'Whatever
                End Select
            Range("E" & r.Row & ":H" & r.Row).Interior.ColorIndex = icolor
            End With
    Next
End Sub

rylo

[robotlust]

Thanks Rylo! BIG HELP.