R
Robert Brydges
Hi there. I want to apply conditional formatting (cell interior and
cell pattern) on 12 or more criteria (values) to the cell containing
the value and the cell offset 1 column to the right, for each cell in a
range A1:A75. Having looked at the archives, the closest I have found
was this code from
Ken Wright:
Private Sub Worksheet_Calculate()
'Code must be placed in the codemodule of the actual sheet you are
working
with.
Dim oCell As Range
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is > 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub
This does everything except the offset to the right. Any suggestions?
Many thanks
Robert
cell pattern) on 12 or more criteria (values) to the cell containing
the value and the cell offset 1 column to the right, for each cell in a
range A1:A75. Having looked at the archives, the closest I have found
was this code from
Ken Wright:
Private Sub Worksheet_Calculate()
'Code must be placed in the codemodule of the actual sheet you are
working
with.
Dim oCell As Range
For Each oCell In Range("A1:A20")
Select Case oCell.Value
Case Is < 1
oCell.Interior.ColorIndex = xlNone
Case Is = 1
oCell.Interior.ColorIndex = 5
Case Is = 2
oCell.Interior.ColorIndex = 3
Case Is = 3
oCell.Interior.ColorIndex = 6
Case Is = 4
oCell.Interior.ColorIndex = 4
Case Is = 5
oCell.Interior.ColorIndex = 7
Case Is = 6
oCell.Interior.ColorIndex = 15
Case Is = 7
oCell.Interior.ColorIndex = 40
Case Is > 7
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub
This does everything except the offset to the right. Any suggestions?
Many thanks
Robert