how can i set more than three conditional formats to xl worksheet

[Guest]

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

 

[Guest]

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.

 

[Gord Dibben]

Would 30 be enough?

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

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


Gord Dibben MS Excel MVP

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

 

[bagger01]

in the code for the sheet try putting the code below, you can add or change and case target:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 to 5
icolor = 6
Case 6 to 10
icolor = 12
Case Else
icolor = 42
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub