Another conditional formatting problem!!!

neilforrow

New Member
Joined
Sep 30, 2004
Messages
16
Morning...

have spent some time looking at old posts and cant find what I am looking for...

I am trying to conditional format a sheet, where, if a a value is entered the cell color changes. So using conditional formatting, easy enough...

although I have 7 different values for 7 different colors, and the conditional formatting seems to only cope with 3 sets of values...

Is there a way of setting up multiple (ie more than 3) conditional formatting conditions are.

eg:

NY = Red
HM = Yellow
KH = Green
EG = Blue
etc...

Thanks for the help in advance...
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hello Neil

You either have to upgrade to xl2007 (where the 3 formats restriction has been removed) or use VBA to perform 'conditional formatting' using worksheet events.

How comfortable with a VBA solution are you? What are the specific cells you are looking to colour in this way?
 
Upvote 0
Unfortunately you are limited to 3 conditional formats in Excel. (4 if you use one of them as the base color)

You can get round this by having some code that physically changes the colour of the font/cells rather than applying a conditional format.

What exactly is it you are trying to do?
 
Upvote 0
Hi guys...

i have a simple calendar for all the members of staff, and when they want to book out equipment they will enter their initials, and for ease of use the colour will change in the cell.. as per my orignal post (NY (Neil Yeandle) = Red) and so on for each member of staff.. there are 7 that will use this sheet at the moment... with more to follow...

Richard... as for VBA... "I know nothing"... I know a little... enough to damage but not enough to fix!!!

thanks...
 
Upvote 0
Neil

Right-click on the worksheet tab name (ie bottom left of main excel interface) and select View Code. Paste the following into the code area that will open up in the VBE:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const myRngAddress = "B3:GA19"
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(myRngAddress)) Is Nothing Then
    Application.EnableEvents = False
    With Target
        Select Case .Value
                Case "NY"
                    .Interior.ColorIndex = 3  'red
                Case "HM"
                    .Interior.ColorIndex = 6   'yellow?
                Case "KH"
                    .Interior.ColorIndex = 4   'green?
                Case "EG"
                    .Interior.ColorIndex = 8   'blue?
                Case "RS"
                    .Interior.ColorIndex = 10   'something else - this is an example
                Case Else
                    .Interior.ColorIndex = xlNone
        End Select
    End With
    Application.EnableEvents = True
End If
End Sub

Now you will have to make additions to the Select Case structure for all the possible values you have (ie the other initials and what colours you want applied to them). Please post back if you are unable to determine how to do this.
 
Upvote 0
Neil,
To get a list of all the availble colors and related numbers
Right-click on the worksheet tab name (ie bottom left of main excel interface) and select View Code. Paste the following into the code area that will open up in the VBE:

Code:
Sub ColorTable()
 ' variables
 Dim i                   As Integer
 Dim j                   As Integer
 Dim k                   As Integer
Dim sColorOrder         As String
Dim sLightColors        As String
Dim arColorOrder        As Variant
Dim iColorNr            As Integer
i = 0
' these are the colors in the same order Excel shows
 ' them in the pulldown:
 sColorOrder = "1,53,52,51,49,11,55,56,9,46,12,10,14," & _
"5,47,16,3,45,43,50,42,41,13,48,7,44,6," & _
"4,8,33,54,15,38,40,36,35,34,37,39,2,17," & _
"18,19,20,21,22,23,24,25,26,27,28,29,30,31,32"
arColorOrder = Split(sColorOrder, ",", , vbTextCompare)
' Light colors that will have a dark fontcolor:
sLightColors = "|6|36|19|27|35|20|28|8|34|2|"
Application.ScreenUpdating = False
For j = 1 To 7            ' loop rows
For k = 1 To 8            ' loop columns
With Cells(j, k)
iColorNr = arColorOrder(i)
.Interior.ColorIndex = iColorNr
.Value = iColorNr
' is the color light, then make the textcolor darker
If InStr(1, sLightColors, "|" & iColorNr & "|") > 0 Then
.Font.ColorIndex = 56  'dark grey
Else
.Font.ColorIndex = 2  'white
End If
End With
i = i + 1
Next k
Next j
 ' Give it a nice layout:
With Range(Cells(1, 1), Cells(7, 8))
.RowHeight = 20
.ColumnWidth = 4
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks to the both of you... I have got the formula to work, thanks...

Richard, as for the input values, I am going to keep it simple and have single entries...

As for getting to colours right... goldenvision, I understand what you are getting at I just cant figure the values out.... what are the Interior.ColorIndex values for the following colours:

Tan
Light yellow
Light Green
Light Turquoise
Pale Blue
lavender
Turquoise

Thanks again for you help guys...
 
Upvote 0
I have just noticed a little gremlin... once you have entered a set on initials ... but you put it in the wrong cell ... you delete the entry but the colour remains... any thoughts....
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top