Running Excel '03, I have a worksheet, which is updated by many users
involved in establishing new contract types. Each line represents a new
contract opportunity. Basic information about each specific contract is keyed
in columns A-L. (M) is blank, and N-AH are columns for completion dates on
specific process stages.

I have (9) different contract types, which are denoted in column J. Based
on the contract type, I need to color in different cells in the N-AH range,
to indicate that these columns, would need to be filled in with dates of
completion.

I am somewhat familiar with formula building, but know that I am limited on
the number of "IF" statements that could be nested. I gave speculation about
creating option buttons, that would connect with macros, to select/color the
specific cells in N-AH, but don't know if this is the best solution.

Does anyone have any suggestions about how to accomplish this? This is a
very high profile project for our company, and all assistance is appreciated.


TIA,
Sandi

does that mean you need 9 different colors? Conditional formatting supports
3 colors in xl2003 and earlier (you say you are running xl2003).

Private Sub Commandbutton1_click()
Dim rw as Long, icolor as Long
Dim r as Range, r1 as Range
rw = activecell.row
set r = range(cells(rw,"N"),cells(rw,"AH"))
set r1 = cells(rw,"J")
select case r1.Value
Case "A"
icolor = 4
Case "B"
icolor = 12
Case "C"
icolor = 3

. . .
Case "I"
icolor = 7

End Select Case
if icolor < 0 then
r.Interior.ColorIndex = icolor
end if
end Sub

--
Regards,
Tom Ogilvy

"RUSH2CROCHET" wrote:

Running Excel '03, I have a worksheet, which is updated by many users
involved in establishing new contract types. Each line represents a new
contract opportunity. Basic information about each specific contract is keyed
in columns A-L. (M) is blank, and N-AH are columns for completion dates on
specific process stages.

I have (9) different contract types, which are denoted in column J. Based
on the contract type, I need to color in different cells in the N-AH range,
to indicate that these columns, would need to be filled in with dates of
completion.

I am somewhat familiar with formula building, but know that I am limited on
the number of "IF" statements that could be nested. I gave speculation about
creating option buttons, that would connect with macros, to select/color the
specific cells in N-AH, but don't know if this is the best solution.

Does anyone have any suggestions about how to accomplish this? This is a
very high profile project for our company, and all assistance is appreciated.


TIA,
Sandi

Thanks for your prompt reply. I don't necessarily need (9) different colors,
just the ability to establish (9) different coloring scenarios. For example,
if column J="N", then color N-AH, if column J="A", then color N-X & AC-AF, if
column, J="S1", then color N-P & W-X, and so on...

"Tom Ogilvy" wrote:

does that mean you need 9 different colors? Conditional formatting supports
3 colors in xl2003 and earlier (you say you are running xl2003).

Private Sub Commandbutton1_click()
Dim rw as Long, icolor as Long
Dim r as Range, r1 as Range
rw = activecell.row
set r = range(cells(rw,"N"),cells(rw,"AH"))
set r1 = cells(rw,"J")
select case r1.Value
Case "A"
icolor = 4
Case "B"
icolor = 12
Case "C"
icolor = 3

. . .
Case "I"
icolor = 7

End Select Case
if icolor < 0 then
r.Interior.ColorIndex = icolor
end if
end Sub

--
Regards,
Tom Ogilvy

"RUSH2CROCHET" wrote:

Running Excel '03, I have a worksheet, which is updated by many users
involved in establishing new contract types. Each line represents a new
contract opportunity. Basic information about each specific contract is keyed
in columns A-L. (M) is blank, and N-AH are columns for completion dates on
specific process stages.

I have (9) different contract types, which are denoted in column J. Based
on the contract type, I need to color in different cells in the N-AH range,
to indicate that these columns, would need to be filled in with dates of
completion.

I am somewhat familiar with formula building, but know that I am limited on
the number of "IF" statements that could be nested. I gave speculation about
creating option buttons, that would connect with macros, to select/color the
specific cells in N-AH, but don't know if this is the best solution.

Does anyone have any suggestions about how to accomplish this? This is a
very high profile project for our company, and all assistance is appreciated.


TIA,
Sandi