color corresponding cell in other column

[Pierre via OfficeKB.com]

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...

 

[Carim]

Hello Pierre,

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

HTH
Cheers
Carim

 

[Norman Jones]

Hi Pierre,

See xlDynamic's CFPlus page at:

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

 

[Guest]

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:

 

[Bob Phillips]

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]

Hi bob,

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

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...

 

[Bob Phillips]

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)

Hi bob,

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

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...

 

[Tom Ogilvy]

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

Hi bob,

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

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...