Colour and Pattern Formatting Cells

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>