Solved: conditional formating offset

hi all,

the following conditional formating works, I need offset the formating column H.


[vba]Private Sub worksheet_selectionchange(ByVal target As Excel.Range)
Dim rnArea As Range
Dim rnCell As Range
Set rnArea = Range("e57:e90")
For Each rnCell In rnArea
With rnCell
If Not IsError(.Value) Then
Select Case .Value
Case "1"
.Interior.ColorIndex = 38
Case "2"
.Interior.ColorIndex = 40
Case "3"
.Interior.ColorIndex = 36
Case "4"
.Interior.ColorIndex = 35
Case "5"
.Interior.ColorIndex = 34
Case "6"
.Interior.ColorIndex = 15
Case "7"
.Interior.ColorIndex = 39
Case "8"
.Interior.ColorIndex = 7
Case "9"
.Interior.ColorIndex = 44
Case "10"
.Interior.ColorIndex = 6
Case "11"
.Interior.ColorIndex = 4
Case "12"
.Interior.ColorIndex = 8
Case "13"
.Interior.ColorIndex = 3
Case "14"
.Interior.ColorIndex = 46
Case "15"
.Interior.ColorIndex = 43

End Select
End If
End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub
[/vba]


any ideas?

thanks

[vba]

Private Sub worksheet_selectionchange(ByVal target As Excel.Range)
Dim rnArea As Range
Dim rnCell As Range
Set rnArea = Range("e57:e90")
For Each rnCell In rnArea
With rnCell
If Not IsError(.Value) Then
Select Case .Value
Case "1"
.Offset(0,3).Interior.ColorIndex = 38
Case "2"
.Offset(0,3).Interior.ColorIndex = 40
Case "3"
.Offset(0,3).Interior.ColorIndex = 36
Case "4"
.Offset(0,3).Interior.ColorIndex = 35
Case "5"
.Offset(0,3).Interior.ColorIndex = 34
Case "6"
.Offset(0,3).Interior.ColorIndex = 15
Case "7"
.Offset(0,3).Interior.ColorIndex = 39
Case "8"
.Offset(0,3).Interior.ColorIndex = 7
Case "9"
.Offset(0,3).Interior.ColorIndex = 44
Case "10"
.Offset(0,3).Interior.ColorIndex = 6
Case "11"
.Offset(0,3).Interior.ColorIndex = 4
Case "12"
.Offset(0,3).Interior.ColorIndex = 8
Case "13"
.Offset(0,3).Interior.ColorIndex = 3
Case "14"
.Offset(0,3).Interior.ColorIndex = 46
Case "15"
.Offset(0,3).Interior.ColorIndex = 43

End Select
End If
End With
Next
Application.Calculation = xlCalculationAutomatic
End Sub
[/vba]

Not tested but perhaps something like[VBA]
If Not IsError(.Value) Then Select Case .Value
Case "1"
.Interior.ColorIndex = 38
.Offset(0,3).interior.colorindex=38
Case "2"
.Interior.ColorIndex = 40

[/VBA]

Ouch! My poor toes.

You're too quick Bob!, i did think he wanted to format the offset TOO!

cant even beat you to a quip!

I'll slow down, wait to see if you are posting (got to do some work anyway, some of us don't have the luxury of working for big corporations).

thats great thanks all.

Not at work, i have a slipped disc and inflammation of the sacrialiac joint!
not sure of the spelling of that.

not too bad now back to work in a week!

There I go, foot in again.

Sorry to hear that, hope it gets better soon. No wonder you are here, anything to take your mind off it!

PS I think it is spelt sacroiliac.

RSS feed?
lol