I'm really stuck on this one and hope I have a new friend out there
that can help me.

I am trying to do a conditional formatting for more than 3 events so I
need to put it in VBA.

Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("I5:I50"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
On Error Resume Next
' -- The line above won't change the cell's background
' -- color if the cell's value is not found in the range
' -- that we specified (rngcolors).
cl.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Value _
, ThisWorkbook.Sheets("Sheet6").Range("rngcolors"), 4,
False)
If Err.Number < 0 Then
cl.Interior.ColorIndex = xlNone
End If
Next cl
End If

End Sub


Few things.....
1. This is not working because the values in I5:I50 are formula
driven, is there a way around this? The formula it the range is:
={SUM((IF(ISERROR((H14-Now)/365*12),-1,(H14-Now)/
365*12)=LEOM_Min)*(IF(ISERROR((H14-Now)/365*12),-1,(H14-Now)/
365*12)<LEOM_Max)*(LEOM_Color))}

Now is a name for the cell with function =now() and I'm taking the
diferrence between two dates (now and a static value for each record)
to find out the number of months. Then I pick the category of months
that I want the color to be. This way say the first 3 months will be
blue, then months 4-12 could be red, etc. So the formula in I5:I50
returns a number, then the vba script should pick up this number and
translate it into the appropriate color formatting as defined in the
range "rngcolors". I thought this would be simple.

2. The next problem is this script will only change the color for the
cell in range I5:I50 when I want it to change the color of the entire
row A5:I50.

As I'm sure you can tell I have done a lot in excel but not much in
VBA. I'm trying to learn.

Thanks in advance for your help.

Greg