PDA

View Full Version : Excel VBA



siva
10-21-2007, 05:38 AM
I would like to change "fill color" base on certain numerical value and change lines based on Alphabet.
The shape is in "Visual" Sheet and the values in "DATA" sheet.

Below is my code.
The fill colors does change according to the values in "Data" sheet but the Line colors does not change.
Anyone can help?
Thanks
Siva


Dim W As String

Private Sub Update_Click()
With Me.Shapes("B01_").Fill.ForeColor
Select Case Sheets("Data").Range("B3").Value
Case 0.01 To 0.28: .SchemeColor = 2
Case 0.29 To 0.3: .SchemeColor = 53
Case 0.31 To 0.5: .SchemeColor = 52
Case 0.51 To 0.9: .SchemeColor = 51
Case 0.91 To 1: .SchemeColor = 17
Case Else: .SchemeColor = 1
If Sheets("Data").Range("J3") = W Then
With Me.Shapes("B01_").Line.ForeColor
Shapes("B01_").Line.DashStyle = msoLineDash
Shapes("B01_").Line.Style = msoLineSingle
Shapes("B01_").Line.Visible = msoTrue
Shapes("B01_").Line.ForeColor.SchemeColor = 2
End With
End If
End Select
End With
End Sub

Bob Phillips
10-21-2007, 07:30 AM
How is W getting set to a value?

lucas
10-21-2007, 07:37 AM
Must be a string... maybe silva forgot the parenthesis

If Sheets("Data").Range("J3") = "W" Then

siva
10-21-2007, 07:37 AM
Pls find the attached file.

siva
10-21-2007, 07:41 AM
add parenthesis to "W" still doesn't work.
I have upload the working file.

Bob Phillips
10-21-2007, 07:46 AM
How is W getting set to a value?

No, t is declared as a variable up top.

Bob Phillips
10-21-2007, 07:47 AM
How about summarising what it is all about, we can't mind-read.

siva
10-21-2007, 08:05 AM
I have numerical data in Sheet "Data"
I have shapes in sheet "Visual"
I want the colours of shapes in sheet "visual" to be changed based on value in sheet "Data" column B to the respective shapes eg B01 to shape B01_
I want the colours of lines of shapes in sheet "visual" to be changed based on value in sheet "Data" column J to the respective shapes eg B01 to shape B01_
When i click the "update" button, the fill colour changes as i want but the line does not change colour.
I hope that you get what i want.

Bob Phillips
10-21-2007, 08:12 AM
Private Sub Update_Click()
With Me.Shapes("B01_").Fill.ForeColor
Select Case Sheets("Data").Range("B3").Value
Case 0.01 To 0.28: .SchemeColor = 2
Case 0.29 To 0.3: .SchemeColor = 53
Case 0.31 To 0.5: .SchemeColor = 52
Case 0.51 To 0.9: .SchemeColor = 51
Case 0.91 To 1: .SchemeColor = 17
Case Else: .SchemeColor = 1
End Select
If Sheets("Data").Range("J3") = "W" Then
With Me.Shapes("B01_").Line.ForeColor
Shapes("B01_").Line.DashStyle = msoLineDash
Shapes("B01_").Line.Style = msoLineSingle
Shapes("B01_").Line.Visible = msoTrue
Shapes("B01_").Line.ForeColor.SchemeColor = 2
End With
End If
End With
End Sub

siva
10-22-2007, 03:50 AM
Thank you! Done!