conditional format greater than 3

What you can do is create a list of all possible entries:

Holiday
H
Sick
Paid Sick
------

Then in the conditional formatting dialog box something like this:

=ISNA(MATCH(A1,List,0))=FALSE

Could you please explain this technique in a little more detail. (regarding the creation of a reference list).

thanks

Joseph G.

Cpod is suggesting that you make a list of possible combinations and name that "List". You could then paste his/her formula in the conditional formatting. The cell will then be conditionally format if the value entered is not found within the list.

Is this explanation sufficient?

Regards,
Barrie

Well, the list itself would have to be created manually. The question was how to conditionally format for numerous text values when there are only three conditional formats allowed. The answer was to lookup on a list of all these values.

thanks for that, but, what i'm trying a achieve is that for each different item it's different colour, this will have to be done in code, as i have said i can write the code so if the cell is ACTIVE it changes to what ever of the 56 colour i want but i need it to do this as a loop or somthing so if i update 1 sheet it automatically updates the FORMAT, I've just strted looking at vb that my prob, regards zen

need more than three colours also, i also need it to update without the cell needing to be active, i.e. when the data is input from another sheet
it need to be done in code, but how????

Hello Zen, I want to clarify what you are trying to do. If I understand you correctly, each time you change a value in your worksheet you want the active cell to change color to reflect its new value. Is that right?

Barrie

thanks for getting back, no, if the value in a different workbook is, say "holiday", then cell linked to that might say "h" (that's fine), therefore the cell that needs formating (colourwise) is never active, it's this that i have a problem with.
because as i understand it:

If ActiveCell.Value = "h" Then
ActiveCell.Format.Interior.ColorIndex = 34
etc
etc
etc

(it's something like that) only works if you physically activate the cell.

cheers zen

This will work, but is not necessarily the most efficient

Zen, if you paste this code in your worksheet code it will change the color based on the IF statements. The range I used for this example was only one column. You can add more IF statements to the code also.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Row_Num

Application.ScreenUpdating = False
Range("Data").Select
Row_Num = ActiveCell.Row - 1
Row_Num = Row_Num + Range("Data").Rows.Count
Do Until ActiveCell.Row > Row_Num
If ActiveCell.Value = "Barrie" Then
ActiveCell.Interior.ColorIndex = 34
ActiveCell.Offset(1, 0).Activate
ElseIf ActiveCell.Value = "Zen" Then
ActiveCell.Interior.ColorIndex = 44
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Interior.ColorIndex = 0
ActiveCell.Offset(1, 0).Activate
End If
Loop
Range("Data").Select
ActiveCell.Select
Application.ScreenUpdating = True

End Sub

Let me know if it works for you.

Regards,
Barrie

Re: This will work, but is not necessarily the most efficient

Application.ScreenUpdating = False Range("Data").Select Row_Num = ActiveCell.Row - 1 Row_Num = Row_Num + Range("Data").Rows.Count Do Until ActiveCell.Row > Row_Num If ActiveCell.Value = "Barrie" Then ActiveCell.Interior.ColorIndex = 34 ActiveCell.Offset(1, 0).Activate ElseIf ActiveCell.Value = "Zen" Then ActiveCell.Interior.ColorIndex = 44 ActiveCell.Offset(1, 0).Activate Else ActiveCell.Interior.ColorIndex = 0 ActiveCell.Offset(1, 0).Activate End If Loop Range("Data").Select ActiveCell.Select Application.ScreenUpdating = True

that seemed to crash excel?? maybe it's me, haven't tried it since, but the way i see the code it still relies on the cell being active, i.e. If ActiveCell.Value = "Barrie" Then. i'm very very new to this vb thing so i'm doubtless wrong. i just want it to look at the cells document if anything changes at all (updating from another sheet etc. and change them.

zen

Re: This will work, but is not necessarily the most efficient

Application.ScreenUpdating = False Range("Data").Select Row_Num = ActiveCell.Row - 1 Row_Num = Row_Num + Range("Data").Rows.Count Do Until ActiveCell.Row > Row_Num If ActiveCell.Value = "Barrie" Then ActiveCell.Interior.ColorIndex = 34 ActiveCell.Offset(1, 0).Activate ElseIf ActiveCell.Value = "Zen" Then ActiveCell.Interior.ColorIndex = 44 ActiveCell.Offset(1, 0).Activate Else ActiveCell.Interior.ColorIndex = 0 ActiveCell.Offset(1, 0).Activate End If Loop Range("Data").Select ActiveCell.Select Application.ScreenUpdating = True

Hi Zen, you are correct that it relies on the cell being active. What I am doing is looping throught the active cells of the range named "Data". I'm not sure why Excel crashed for you, it didn't for me. I can only suggest to try again. If there is anything you don't understand in the code just let me know.

Barrie

Re: This will work, but is not necessarily the most efficient

i stiill don't understand, where does the "data" part come in, do i have to put this on the sheet somwhere?? it just seems to run and end with a1 being active, as i said i am a complete novice at this malarky so thanks for being patient.

zen

also!!

while i'm being a pain, how do i tell workbooks to enable macros & update links automatically, as nearly every 5 mins people are asking me if they should do these things, even though i musta told em a hundred times, cheers zen

tracked some code down, still no joy

further to the problems i have, scouring these here pages i stumbled upon a peice of code from dave:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rWatchrange As Range
'Written by OzGrid Business Applications
'www.ozgrd.com
'''''''''''''''''''''''''''''''''''''''
'Allows more than 3 Conditional Formats
''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Then Exit Sub
Set rWatchrange = Range("A1:C100")
On Error Resume Next
If Intersect(Target, rWatchrange) Is Nothing Then
Set rWatchrange = Nothing
Exit Sub
End If
Select Case Target
Case 1 To 5
Target.Interior.ColorIndex = 6
Case 6 To 10
Target.Interior.ColorIndex = 46
Case 11 To 15
Target.Interior.ColorIndex = 5
End Select
End Sub

this seem to work nicely, in terms of the fact that it changes the cell color on exit, except it still doesn't work if the data come from, say, sheet2!, it seems not to be looking at the value in the cell, but rather the =sheet2!a1 formula,
i have adapted the case to "uh" "ph" etc.
is Case the right thing???????????

zen

Re: This will work, but is not necessarily the most efficient

Hi again Zen. I have provided comments with each line.

Dim Row_Num
'Declares a variable

Application.ScreenUpdating = False
'Turns off the screen so you don't see what it is doing
Range("Data").Select
'Selects the range named data
Row_Num = ActiveCell.Row - 1
'Sets the variable Row_Num equal to the row number of the top left cell in the range named data
Row_Num = Row_Num + Range("Data").Rows.Count
'Adds the number of rows in the range named date to the variable Row_Num (declared above)
Do Until ActiveCell.Row > Row_Num
'Performs the following UNTIL the row number is greater than Row_Num
If ActiveCell.Value = "Barrie" Then
'If the value of the active cell = Barrie then perform the following
ActiveCell.Interior.ColorIndex = 34
'Set the active cell's interior color to #34
ActiveCell.Offset(1, 0).Activate
'Go down one row to the next cell and activate it
ElseIf ActiveCell.Value = "Zen" Then
'If the value of the active cell = Zen then perform the following
ActiveCell.Interior.ColorIndex = 44
'Set the active cell's interior color to #44
ActiveCell.Offset(1, 0).Activate
'Go down one row to the next cell and activate it
Else
'Otherwise
ActiveCell.Interior.ColorIndex = 0
'Set the active cell's interior color to #0
ActiveCell.Offset(1, 0).Activate
'Go down one row to the next cell and activate it
End If
'Marks the end of the IF statements
Loop
'Marks the end of the DO statement
Range("Data").Select
'Select the range named data
ActiveCell.Select
'Select the top left hand cell of the range named data
Application.ScreenUpdating = True
'Turns the screen updating back on

Hope this will help you out.

Barrie

Re: also!!

This is something that you can't do (sorry to be the bearer of bad news).

Barrie

Re: This will work, but is not necessarily the most efficient

i have pasted the code into the sheet, when i returned to excel and activated a cell, the code ran? and for a very long time? the problem i'm having is that i need this to look @ aprx 12,000 cells and then on 14 sheets.

zen

p.s your patients is most appreciated

Re: This will work, but is not necessarily the most efficient

zen p.s your patients is most appreciated

Zen, it will definitely take a long time because it is looping through each cell. This is, however, a really interesting problem (and I'm pig-headed) so I'm going to keep at it.

Barrie.