More than 3 conditional formats

[Sir Babydum GBE]

Hi again

Sheet 1, column A (1 to 100) has a list of codes which have all been formatted differently (cell and font colours, that is). (say "MyCode1" is red cell with white font; and "MyCode2" is light blue cell with black font etc etc)

Sheet 2 - all of it - has validation in every cell, so the user can only input one of the codes that appears on sheet 1.

How can I get excel to read the formatting in the cell that contains the identical code and apply the formatting into sheet 2 so that when the user inputs "MyCode1", for instance, it turns red with white font...

I know from Jake's KB article I could manually specify all the entries in the sheet code and assign colours - but there are two problems here:

1) I'm using Excel 97, and selecting an entry from a validated drop-down doesn't trigger a macro (unless I use Worksheet_Calculate - if I remember from a previous post)
2) I need flexiility - I need to be able to change colour schemes without going into the code and changing 100 lines each time.

Any help greatly appreciated. Thanks alot

Sir BD

[lenze]

How many different font/background schemes do you have?

lenze

[Sir Babydum GBE]

May get around 50... ish.

[lenze]

A little off the wall, and some upfront work required, but maybe this will give you an idea. Assign each of your 50ish fonts a Style name (Format>Styles), myCode1,myCode2,etc....

Now use a Cell (B1 in my example) to choose a style by number. Then you can use these two codes together.
[vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not WorksheetFunction.IsNumber(Range("$B$1")) Then Exit Sub
If Range("$B$1").Value > 50 Then Exit Sub
Target.Style = "myCode" & Range("$B$1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$B$1" Then Exit Sub
Dim myStyle As Integer
myStyle = Target.Value
Target.Style = "myCode" & myStyle
End Sub[/vba]

When a number is entered in B1, the Change event will assign that style to B1. Now when you double click on other cells in the worksheet, they will also receive that style.

Not perfect, but maybe a start.

lenze