Hi experts,

here's my problem:

in column "O" i have words like Closed, quote, Negotiation, Lost
Now, when a cell in column O is changed, I would like to color the
corresponding cell in column D to

closed = green
quote = blue
Negotiation = orange
Lost = red

Do you have any idea how to solve this in VBA please?
Thanks,
Pierre

P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Hello Pierre,

Think you shoud take a look at
http://rhdatasolutions.com/ConditionalFormatVBA/

HTH
Cheers
Carim

Hi Pierre,

See xlDynamic's CFPlus page at:

http://www.xldynamic.com/source/xld.....Download.html


---
Regards,
Norman


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:5999f8b5421e8@uwe...
Hi experts,

here's my problem:

in column "O" i have words like Closed, quote, Negotiation, Lost
Now, when a cell in column O is changed, I would like to color the
corresponding cell in column D to

closed = green
quote = blue
Negotiation = orange
Lost = red

Do you have any idea how to solve this in VBA please?
Thanks,
Pierre

P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

I think that u should use conditional formating. In your case it's possible
to set those formats. Format cells in one column on clasic way and then set
conditions.
Example
set red colour for all cells that are in specified range, and
then in coditional formating set
closed = green
quote = blue
Negotiation = orange

€žPierre via OfficeKB.com€œ je napisao:

Hi experts,

here's my problem:

in column "O" i have words like Closed, quote, Negotiation, Lost
Now, when a cell in column O is changed, I would like to color the
corresponding cell in column D to

closed = green
quote = blue
Negotiation = orange
Lost = red

Do you have any idea how to solve this in VBA please?
Thanks,
Pierre

P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Pierre,

You need more than 3, but only 4. You could set them all to a default
colour, say green for closed, and then use the 3 formats in CF to test for
other values.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:5999f8b5421e8@uwe...
Hi experts,

here's my problem:

in column "O" i have words like Closed, quote, Negotiation, Lost
Now, when a cell in column O is changed, I would like to color the
corresponding cell in column D to

closed = green
quote = blue
Negotiation = orange
Lost = red

Do you have any idea how to solve this in VBA please?
Thanks,
Pierre

P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Hi bob,

actually, i need more then 4 but listed only 4 in my question...
Please help...
Pierre

Bob Phillips wrote:
Pierre,

You need more than 3, but only 4. You could set them all to a default
colour, say green for closed, and then use the 3 formats in CF to test for
other values.

Hi experts,

[quoted text clipped - 15 lines]
P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

You have already been given two solutions that would work, from Norman and
from Carim.

I would use the worksheet event code


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "Lost": .Interior.ColorIndex = 3 'red
Case "Negotiation": .Interior.ColorIndex = 46 'orange
Case "quote": .Interior.ColorIndex = 5 'blue
Case "closed": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:599b361b77fc4@uwe...
Hi bob,

actually, i need more then 4 but listed only 4 in my question...
Please help...
Pierre

Bob Phillips wrote:
Pierre,

You need more than 3, but only 4. You could set them all to a default
colour, say green for closed, and then use the 3 formats in CF to test
for
other values.

Hi experts,

[quoted text clipped - 15 lines]
P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

If the changes in column O are by editing the cell, then right click on the
sheet tab and select view code. Paste in code like this:

Private Enum icolor
green = 4
blue = 5
yellow = 6
orange = 45
red = 3
End Enum


Private Sub Worksheet_Change(ByVal Target As Range)
Dim icol As icolor
Dim cell As Range
If Target.Columns.Count = 1 And Target.Column = 15 Then
For Each cell In Target
Select Case LCase(cell.Value)
Case "closed": icol = green
Case "quote": icol = blue
Case "negotiation": icol = orange
Case "lost": icol = red
End Select
Cells(cell.Row, 4).Interior.ColorIndex = icol
Next
End If
End Sub

This assumes xl2000 or later.

--
Regards,
Tom Ogilvy

"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:599b361b77fc4@uwe...
Hi bob,

actually, i need more then 4 but listed only 4 in my question...
Please help...
Pierre

Bob Phillips wrote:
Pierre,

You need more than 3, but only 4. You could set them all to a default
colour, say green for closed, and then use the 3 formats in CF to test
for
other values.

Hi experts,

[quoted text clipped - 15 lines]
P.S. conditional formatting does not work beause i need more than three
colors...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1