Conditional Formatting

[Oldersox]

How would I apply conditional formats (up to 9 types) where the format is
applied to a range of cells but not the cell with the controlling condition.

eg. if cell A9=1 cells A1:A7 format as yellow
if cell A9=2 cells A1:A7 format as blue
etc.....for 9 different colours

Ther must also be an allowance for the target cell to have 'blank' and no
formatting applies
if cell A9=' ' cells A1:A7 have no fomatting

In all cases the cell with the controlling condition will be to the
immediate right (on the same row) as the cells that the formatting is to be
applied to

note: I have seen the add-in that allows up to 30 conditional formats but I
am on a controlled system and cannot install this (I have already asked IT
and they have refused)

Any and all suggestions welcome
Regards
Geoff (Oldersox)

 

[Barb Reinhardt]

If you are using Excel 2007, you can do this fairly easily. If you're on
2003, you'll have to do it with VBA. For now, I'm going to assume it's 2003.

Right click on the worksheet tab of interest and select View Source. Paste
this in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myColorIndex As XlColorIndex
Set myRange = Me.Range("A1:A7")

If Intersect(Target, Me.Range("A9")) Is Nothing Then Exit Sub

'Find the colorindex colors here
'http://www.mvps.org/dmcritchie/excel/colors.htm

Select Case Me.Range("A9")
Case 1
myColorIndex = 6
Case 2
myColorIndex = 5
Case 3
myColorIndex = 4
Case 4
myColorIndex = 3
Case 5
myColorIndex = 36
Case 6
myColorIndex = 35
Case 7
myColorIndex = 34
Case 8
myColorIndex = 33
Case 9
myColorIndex = 32
Case Else
myColorIndex = xlColorIndexNone
End Select

myRange.Interior.ColorIndex = myColorIndex

End Sub