Hi I have found the following VBA code to allow me to have more than 3 conditional formats. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F22:K5000")) Is Nothing Then Select Case Target Case "Critical" icolor = 3 Case "High" icolor = 45 Case "Moderate" icolor = 6 Case "Low" icolor = 4 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub I am using this in a spreadsheet that records incidents. There are four levels of risk that could be assigned to an incident and each level of risk has a different colour. The user selects values from lists in two cells and the risk shows up in the third cell thanks to a formula. So if likelihood = rare & consequence = minor then risk will show up as low. What this means is that the cell with the risk is not resulting in a change that triggers the code. If I F2 into the risk cell and then hit enter it triggers the code and then colours the cell appropriately. Can anyone suggest something to fix this problem. I hope this makes sense...its my first time asking a question!!!! Thanks...Tee

Your "Target" is always the cell that the user changes, not cells that are changed by a formula. Consider 100 cells with formulae all dependent on a single cell that a user changes. They can't *all* be the Target cell. You need to modify your code to determine the Target cell, but to change the cell with the formula in it. If you know where the last user change will be made, you can use OFFSET to change a different cell: Select Case Target.Offset(0, 1) Case 1... Case 2... Target.Offset(0, 1).Interior.ColorIndex = icolor If all you know is the Row (or Column) of the Target cell, but not the actual cell, you could use something like: Select Case Cells(Target.Row, 2) Case 1... Case 2... Cells(Target.Row, 2).Interior.ColorIndex = icolor