more than 3 conditional formats

Posted by steve on February 07, 2001 10:16 PM

I need some help writing a function to do more that 3 conditional formats. I want to be able to use it multiple times on a worksheet and have its target cell be variable so I will be able to change the target cell from the worksheet.( Kind of like how you can target cells using many of excels built in functions.)

thanks steve

Posted by Dave Hawley on February 08, 2001 12:45 AM

Hi Steve

You could use the Worksheet_Change event for this. Right click on the sheet name tab and select "View Code" and paste in the code below. Just change A1:A10 to the range you want validated and also the limits and Color Index numbers.

Get the list of colors you need simply record a macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
Target.Interior.ColorIndex = 6
Case 6 To 11
Target.Interior.ColorIndex = 46
Case 12 To 17
Target.Interior.ColorIndex = 5
Case 18 To 23
Target.Interior.ColorIndex = 15
Case 24 To 29
Target.Interior.ColorIndex = 50
End Select
End If
End Sub

Do the same for each Worksheet.

Hope this helps

Dave

OzGrid Business Applications