0

my code is to run through a table and store the cell color of column D while also storing the value of in column C as another variable. These variables are used to find a shape on another the "main" tab and update that color to the color that was stored in CellColor. When I added the loop part of the code I get an out of bounds error (-2147024809 (80070057)).

Sub Update()
Dim CellColor As Long
Dim ShapeColor As Variant
Dim rng As Range, Cell As Range
Dim i As Integer

Worksheets("Sheet1").Select
Set rng = Range("C2:C100")
For i = 2 To rng.Rows.Count
CellColor = rng.Cells(RowIndex:=i, ColumnIndex:="D").DisplayFormat.Interior.Color
ShapeColor = rng.Cells(RowIndex:=i, ColumnIndex:="C").Value
Worksheets("main").Shapes(ShapeColor).Fill.ForeColor.RGB = CellColor
i = i + 1
Next
Worksheets("main").Select
End Sub

enter image description here

enter image description here

bullfrog97
  • 53
  • 7
  • 1
    Remove the `i = i + 1`. `Next` is what increments `i`. – BigBen Aug 20 '20 at 17:34
  • 1
    You may also want to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) instead of hard-coding the 100. – BigBen Aug 20 '20 at 17:36
  • @BigBen I removed the `i=i+1` but it I still get the error, I will post the error into the question. I hard coded the end of the range because this range will not change. – bullfrog97 Aug 20 '20 at 17:44
  • 1
    What is the value of `ShapeColor` when the error occurs? – BigBen Aug 20 '20 at 17:48
  • it is "empty" but if I take the loop portion off it will run only if the sheet with the table is selected. it will throw the same error if I'm on the tab with the shapes. – bullfrog97 Aug 20 '20 at 18:20
  • when I debug it highlights `Worksheets("main").Shapes(ShapeColor).Fill.ForeColor.RGB = CellColor` – bullfrog97 Aug 20 '20 at 18:21
  • 2
    Your range is column C, but you ask for column C and D – Mike67 Aug 20 '20 at 18:29

1 Answers1

1

Perhaps use a For Each loop here and Offset:

Set rng = Worksheets("Sheet1").Range("C2:C100")

Dim cell As Range
For Each cell In rng
    ShapeColor = cell.Value
    CellColor =  cell.Offset(,1).DisplayFormat.Interior.Color
    Worksheets("main").Shapes(ShapeColor).Fill.ForeColor.RGB = CellColor
Next

A brief explanation of your problem:

rng.Cells(RowIndex:=i, ColumnIndex:="C")
rng.Cells(RowIndex:=i, ColumnIndex:="D")

are not the cells you think they are, because they are offsetting but starting from column C. They are actually referring to columns E and F.

As an example: ? Range("C2").Cells(1, "C").Address returns $E$2, not $C$2.

Other points:

  • Remove the i = i + 1. Next is what increments i.
  • Avoid using Select: Set rng = Worksheets("Sheet1").Range("C2:C100").
BigBen
  • 46,229
  • 7
  • 24
  • 40