worksheet_change

[aravindvin]

Hi,

I have attached the excel with code.

Based on the drop down list change in column G and H, the values i
C,D,E & F will change from 1 through 5.

Based on the numberic value in C,D,E & F, the color in it should chang


You should be able to set different colors for values= 1,2,3,4,5.

NOTE: Currenlty only cells in C are changing if the number in i
changes to 1,2, or 3

Pls. Let me know!!!!

CODE below::::
=========================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("C:F")) Is Nothing Then
Select Case Target
Case 0
icolor = 2
Case 1
icolor = 56
Case 2
icolor = 22
Case 3
icolor = 27
Case 4
icolor = 4
Case 5
icolor = 10
Case Else
icolor = 2
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
=========================

Attachment filename: colortest v9.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66019

 

[Bob Phillips]

It seems to work fine for me. Although I don't have the link to G&H, if I
enter 1 in C it goes gray, so does D, and E, and F. Not at the same time,
but they all are triggering the event.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[aravindvin]

Please note that we cannot change the values in cloumns C,D,E and F.

The values in these cloumns are determined after the drop down select
in column G and H.

Aravin

 

[Bob Phillips]

What version of Excel, 97 by any chance?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[aravindvin]

I am using Excel 2002 version

Let me clarify few points:
1.
Columns in C,D,E and F are not to be manually entered. These value
are determined by changes dropdown list in column G and H

2. The color in C,D,E and F has to change based on the integer that i
gets after dropdown list change in G/H.

3. Currenlty changes are reflected in Col C only.

4. Although integers 4 and 5 have some diff colors, they both ar
getting allocated only grey color.

Any help will be greatly appreciated.

Aravind

 

[Bob Phillips]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim icolor As Integer

If Not Intersect(Target, Range("G:H")) Is Nothing Then
For Each cell In Cells(Target.Row, 3).Resize(1, 4)
Select Case cell
Case 0: icolor = 2
Case 1: icolor = 56
Case 2: icolor = 22
Case 3: icolor = 27
Case 4: icolor = 4
Case 5: icolor = 10
Case Else: icolor = 2
End Select
cell.Interior.ColorIndex = icolor
Next cell
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)