How to set a VBA variable to a cell object instead of the cell contents?

Asked May 30 '23 at 10:05

Active May 30 '23 at 10:05

Viewed 24 times

I have loop where I work with different cells. As part of this loop I want to set cell color, however I am forced to use the full cell address instead of just a variable assigned. Below is a sample in one iteration of the loop.

Doesn't work with "Run-time error '424': Object required

ColorMe = SheetCheck.Cells(1, 1) 'ColorMe is the value in the Cell 1,1
ColorMe.Interior.Color = vbGreen

works

SheetCheck.Cells(1, 1).Interior.Color = vbGreen

asked May 30 '23 at 10:05

OrigamiEye

1

Use `Set ColorMe = ...` – chris neilsen May 30 '23 at 10:07
You need to declare ColorMe as a range (Dim ColorMe as Excel.Range) and then use Set when assigning the range to ColorMe (Set ColorMe = SheetCheck.Cells(1,1)) – freeflow May 30 '23 at 10:08

How to set a VBA variable to a cell object instead of the cell contents?

0 Answers0