Solved: conditional formating offset

[CCkfm2000]

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

[Bob Phillips]

[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]

[Simon Lloyd]

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]

[Bob Phillips]

Ouch! My poor toes.

[Simon Lloyd]

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

[Simon Lloyd]

cant even beat you to a quip!

[Bob Phillips]

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).

[CCkfm2000]

thats great thanks all.

[Simon Lloyd]

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!

[Bob Phillips]

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.

[Simon Lloyd]

RSS feed?
lol