CODEWITHSUNDEEP
excelvbacolors
matandked
matandked

Reputation: 1573

VBA - cells color

What is wrong with this code? I get an "ARG!" error

Public Function nr_kolor(kom As Range)  
  For Each komorka In kom
    wartosc = komorka.Font.Color
    wiersz = komorka.Row
    kolumna = komorka.Column + 3
    nr_kolor = wartosc
  Next komorka
  activesheet.Cells(wiersz, kolumna).Select
  Selection.Interior.Color = wartosc  
End Function

Upvotes: 0

Views: 2698

Answers (1)

Gaijinhunter
Gaijinhunter

Reputation: 14685

There are a lot of things you are doing wrong.

  • You have failed to declare your variables.
  • You need to use a Sub() not Function(). As a rule, functions are not allowed to alter the worksheet in any way, just manipulate and return values.
  • Your loop has errors - if you wish to do things to more than one cell than all your instructions need to be within the loop. Right now it only colors 1 cell (3 columns right of the LAST cell in your range).
  • You are returning the function before the function is over.

I think what you are trying to do (there was no explanation) is take a range of cells, and apply the same interior color to the cells three columns over. Here is a more effecient way to do this. I have purposly kept the logic simple.

Sub ColorCells()

Dim cell As Range

For Each cell In Range("A1:A10")
    cell.Offset(, 3).Interior.Color = cell.Interior.Color
Next

End Sub

How it works: You create a variable cell as a range, this will represent each cell in the range you supply. For each cell in the range, you want to apply the same interior color to the cell OFFSET 3 columns to the right.

Upvotes: 3

Related Questions