worksheet_change

  • Thread starter Thread starter aravindvin
  • Start date Start date
A

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
 
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)
 
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
 
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)
 
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
 
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)
 
Back
Top