I am trying to set up a sheet showing recurring staff absences. i can only do
Conditional Formats three times on one sheet. With more than three types of
absence I would like to know if ther is a way to increase the number of
Conditional Formats I can make

Andy,

You can only get more than 4 in code (as far as I am aware - 4 not 3 as
there is the default no formatting).

You add code to the code module of the worksheet. The following example
would assume that you had put the cells you wanted conditionally formatted in
a named range called 'conditional' & does a basic background colour change
based on the values.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ranCell As Excel.Range

For Each ranCell In Application.Intersect(Target, Me.Range("conditional"))

With ranCell
Select Case .Value

Case 1

.Interior.Color = vbBlue

Case 2

.Interior.Color = vbYellow

Case 3

.Interior.Color = vbRed

Case 4

.Interior.Color = vbGreen

End Select

End With

Next ranCell

End Sub

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Andy A112" wrote:

I am trying to set up a sheet showing recurring staff absences. i can only do
Conditional Formats three times on one sheet. With more than three types of
absence I would like to know if ther is a way to increase the number of
Conditional Formats I can make

Would 30 be enough?

Bob Phillips' site has a downloadable free add-in called CFPlus which does this.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 08:58:03 -0700, Andy A112 <Andy
wrote:

I am trying to set up a sheet showing recurring staff absences. i can only do
Conditional Formats three times on one sheet. With more than three types of
absence I would like to know if ther is a way to increase the number of
Conditional Formats I can make