CODEWITHSUNDEEP
vbaexcel
Johannes Graulich
Johannes Graulich

Reputation: 15

reading a range value from a cell

in the following code

Sub SetColorScheme(cht As Chart, i As Long)

    Dim y_off As Long, rngColors As Range
    Dim x As Long

    y_off = i Mod 10
    'this is the range of cells which has the colors you want to apply
    Set rngColors = ThisWorkbook.Sheets("colors").Range("A1:C1").Offset(y_off, 0)

    With cht.SeriesCollection(1)
        'loop though the points and apply the 
        'corresponding  fill color from the cell
        For x = 1 To .Points.Count
            .Points(x).Format.Fill.ForeColor.RGB = _
                             rngColors.Cells(x).Interior.Color
        Next x
    End With

End Sub

the range from which the data are read is in th emoment stated in the code. Is there a chance that it is read from asheet in the worksheet? So that a person can enter A1:C1 and it will place it the way it is in the code in the moment?

Upvotes: 0

Views: 212

Answers (2)

Andy G
Andy G

Reputation: 19367

If the user enters "A1:C1" in cell D1 then you can make use of this range with:

Set rngColors = ThisWorkbook.Sheets("colors").Range(Range("D1").Value).Offset(y_off, 0)
' but you should refer to the w/sheet as well

Set rngColors = ThisWorkbook.Sheets("colors") _ 
    .Range(ThisWorkbook.Sheets("colors").Range("D1").Value).Offset(y_off, 0)

Range("D1").Value obtains the text "A1:C1" which is then used to identify this Range.

Upvotes: 0

Smandoli
Smandoli

Reputation: 7019

I'm not sure how you want to handle the user's input, but of course the range can be an incoming variable. I have it below as a string but elegance would be the range object. Sorry if this is too simple, I'm not sure your question. 

Sub SetColorScheme(UserRange As String, cht As Chart, i As Long)
    ...

    'this is the range of cells which has the colors you want to apply
    Set rngColors = ThisWorkbook.Sheets("colors").Range(UserRange).Offset(y_off, 0)

    ...    
End Sub

Upvotes: 1

Related Questions