N
Newbrier
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
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