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

On Sep 19, 8:19 pm, Newbrier wrote:
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

On Sep 19, 8:19 pm, Newbrier wrote:
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

Greg, the code below will highlight the entire row based on the text
in I. However, since I is made up of formulas, you probably want to
change the rng to that of the range that would be getting changed.
Your CSE formula references column H. If that the column that is
actually being changed? If so, you could change rng to reference
column H instead of I.

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
Dim rng As Range, cl As Range
Set rng = Intersect(Target, Range("I5:I50"))
If rng Is Nothing Then
Exit Sub
Else
For Each cl In rng
On Error Resume Next
cl.EntireRow.Interior.ColorIndex = _
Application.WorksheetFunction.VLookup(cl.Text _
, ThisWorkbook.Sheets("Sheet6").Range("rngcolors"), 4,
False)
If Err.Number < 0 Then
cl.EntireRow.Interior.ColorIndex = xlNone
End If
Next cl
End If
End Sub