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)