Conditional Formatting of Drop down lists >3

[brayleyboy]

Hi, I have a formatting problem. I have created drop down list using data validation. I would like the cell to format to different fill colours depending on the option chosen. I have acheived this using conditional formatting. However my dropdown lists contains 5 options, and conditional formatting only allows 3. Is there another way of doing this?

Thanks

 

[Norie]

The only way oif doing conditional formatting with more than 3 conditions is via VBA using a worksheet event like Change.

Unfortunately I think it's only in later versions of Excel that an event is triggered by a selection from data validation.

What version of Excel do you have?

The code would look something like this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$A$1" Then Exit Sub
    
    Select Case Target.Value
        Case 1
            ' format as desired for option 1
        Case 2
            ' format as desired for option 2
        Case 3
            ' format as desired for option 3
        Case 4
            ' format as desired for option 4
        Case 5
            ' format as desired for option 5
    End Select
    
End Sub

 

[brayleyboy]

I have excel 97 SR-2.

 

[Norie]

Well I'm sorry but as far as I know you won't be able to use VBA for this.