I've got a list of 37 condition statements which i've combined into a
valdiated drop down list, but I need the cell adjacent to the list to dispaly
one of 37 unique colour a pattern combinations to allow mutiple selections on
the sheet to be easily read to assess commonality. Any sugestions as to how I
do this, I'm pretty new to VB in Excel2003.

Simplest case is an 8Rx2C matrix with column 1 being the output in terms of
shading and patterns and column 2 being the drop down selection.

Thanks for any help
Dave

Just a couple of pointers:

To get code for a particular colour/pattern, just use the recorder (Tools,
Macro, Record New Macro) and tidy as necessary.

To colour according to a multiple selection, use a loop combined with If, eg:

Sub MultipleFormat()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value = [...] Then
Cells(myCell.Row, 1).Font.ColorIndex = [...]
ElseIf myCell.Value = [...] Then
[...]
Else
[...]
End If
Next
End Sub

Hope that helps.

"Dave M" wrote:

I've got a list of 37 condition statements which i've combined into a
valdiated drop down list, but I need the cell adjacent to the list to dispaly
one of 37 unique colour a pattern combinations to allow mutiple selections on
the sheet to be easily read to assess commonality. Any sugestions as to how I
do this, I'm pretty new to VB in Excel2003.

Simplest case is an 8Rx2C matrix with column 1 being the output in terms of
shading and patterns and column 2 being the drop down selection.

Thanks for any help
Dave

Hi Dave,

Following assumes you have named a range for your DV list "mylist" and a
range for your formats named "formats". The range "formats" would be in the
column left of "mylist" and same size, though it could be anywhere. Also
assumes your dropdown list is in D5 and by adjacent you mean one cell to the
right.

In the Worksheet module (rt-click sheet tab view code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vRow
Dim vClrIdx
On Error GoTo errH
If Target.Address = "$D$5" Then ' address of the DV dropdown list
vRow = Application.Match(Target.Value, Range("mylist"), 0)
If IsNumeric(vRow) Then
vClrIdx = Range("formats")(vRow, 1).Value
If IsNumeric(vClrIdx) = False Or vClrIdx < 1 Or vClrIdx 56
Then
vClrIdx = xlNone
End If
End If
Target.Offset(, 1).Interior.ColorIndex = vClrIdx
End If
errH:
End Sub

This assumes you have entered colorindex numbers in the range "formats". If
instead you have actually formated cells in "formats" you could do something
like

Target.Offset(, 1).Interior.ColorIndex = Range("formats")(vRow,
1).Colorindex
and similar for .Pattern and .PatternColorIndex if necessary.

Would need more error handling.

Regards,
Peter T

"Dave M" wrote in message
...
I've got a list of 37 condition statements which i've combined into a
valdiated drop down list, but I need the cell adjacent to the list to
dispaly
one of 37 unique colour a pattern combinations to allow mutiple selections
on
the sheet to be easily read to assess commonality. Any sugestions as to
how I
do this, I'm pretty new to VB in Excel2003.

Simplest case is an 8Rx2C matrix with column 1 being the output in terms
of
shading and patterns and column 2 being the drop down selection.

Thanks for any help
Dave

Peter T and Martin

Many thanks, I'll give it a go, I'd got about halfway there but was stuck
for ideas!
Dave

"Peter T" wrote:

Hi Dave,

Following assumes you have named a range for your DV list "mylist" and a
range for your formats named "formats". The range "formats" would be in the
column left of "mylist" and same size, though it could be anywhere. Also
assumes your dropdown list is in D5 and by adjacent you mean one cell to the
right.

In the Worksheet module (rt-click sheet tab view code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vRow
Dim vClrIdx
On Error GoTo errH
If Target.Address = "$D$5" Then ' address of the DV dropdown list
vRow = Application.Match(Target.Value, Range("mylist"), 0)
If IsNumeric(vRow) Then
vClrIdx = Range("formats")(vRow, 1).Value
If IsNumeric(vClrIdx) = False Or vClrIdx < 1 Or vClrIdx 56
Then
vClrIdx = xlNone
End If
End If
Target.Offset(, 1).Interior.ColorIndex = vClrIdx
End If
errH:
End Sub

This assumes you have entered colorindex numbers in the range "formats". If
instead you have actually formated cells in "formats" you could do something
like

Target.Offset(, 1).Interior.ColorIndex = Range("formats")(vRow,
1).Colorindex
and similar for .Pattern and .PatternColorIndex if necessary.

Would need more error handling.

Regards,
Peter T

"Dave M" wrote in message
...
I've got a list of 37 condition statements which i've combined into a
valdiated drop down list, but I need the cell adjacent to the list to
dispaly
one of 37 unique colour a pattern combinations to allow mutiple selections
on
the sheet to be easily read to assess commonality. Any sugestions as to
how I
do this, I'm pretty new to VB in Excel2003.

Simplest case is an 8Rx2C matrix with column 1 being the output in terms
of
shading and patterns and column 2 being the drop down selection.

Thanks for any help
Dave