CODEWITHSUNDEEP
excelvbavector
Sharat V Chandrasekhar
Sharat V Chandrasekhar

Reputation: 101

Using a Colour-Vector to Fill Cell Colours in one Swoop

I want to store the colours RGB(0,0,0) to RGB(255,255,255) of a greyscale map in a vector of type Long and then do a block cell colour fill into a column of an Excel spreadsheet. The last command in the VBA macro does not work. The error message that I get is:

Run-time error '13': Type mismatch

What am I doing wrong? Any suggestions? Thanks

Sub GreyMap()
    Dim cvec(256) As Long, rng As Range, i%, k%
    Set rng = Range("C1:C256")
    k = 0
    For i = 0 To 255 ' Loop over main diagonal of RGB cube
     k = k + 1
        cvec(k) = RGB(i, i, i) ' Shades of Grey
        rng.Rows(k) = cvec(k)
      '  rng.Rows(k).Interior.Color = cvec(k)  ' This works, but I don't want to do a cell-by-cell write
    Next i
    rng.Interior.Color = cvec        ' Is this even a legal VBA command ?
End Sub

Upvotes: 0

Views: 35

Answers (2)

Sharat V Chandrasekhar
Sharat V Chandrasekhar

Reputation: 101

Upon reflection, rather than actually answer it, I retract my question as posed, because if I wanted to fill the entire range with a single colour, then I'd have to use the same command, the RHS of which must be a scalar and hence cannot also be a vector.

That being said, is there by any chance a variant of the .interior.color syntax that does indeed allow for vector fill?

Upvotes: 0

cybernetic.nomad
cybernetic.nomad

Reputation: 6398

I don't think you need such a complex approach

Sub GreyMap()
    Dim i As Long
    For i = 0 To 255
        Range("C" & i + 1).Interior.Color = RGB(i, i, i)
    Next i
End Sub

Upvotes: 0

Related Questions