Conditional Formatting of Drop down lists >3

brayleyboy

Board Regular
Joined
Nov 24, 2005
Messages
52
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Well I'm sorry but as far as I know you won't be able to use VBA for this.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,216
Members
412,448
Latest member
ManuW
Top