PDA

View Full Version : Solved: conditional formating offset



CCkfm2000
05-15-2007, 03:19 AM
hi all,

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


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



any ideas?

thanks

Bob Phillips
05-15-2007, 03:24 AM
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

Simon Lloyd
05-15-2007, 03:25 AM
Not tested but perhaps something like
If Not IsError(.Value) Then Select Case .Value
Case "1"
.Interior.ColorIndex = 38
.Offset(0,3).interior.colorindex=38
Case "2"
.Interior.ColorIndex = 40

Bob Phillips
05-15-2007, 03:25 AM
Ouch! My poor toes.

Simon Lloyd
05-15-2007, 03:26 AM
You're too quick Bob!, i did think he wanted to format the offset TOO!

Simon Lloyd
05-15-2007, 03:27 AM
cant even beat you to a quip!

Bob Phillips
05-15-2007, 03:30 AM
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
05-15-2007, 03:33 AM
thats great thanks all.

Simon Lloyd
05-15-2007, 03:34 AM
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
05-15-2007, 03:40 AM
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
05-15-2007, 03:43 AM
RSS feed?
lol