CODEWITHSUNDEEP
vba
Jonathan Hutchison
Jonathan Hutchison

Reputation: 11

VBA - Error when using ActiveCell and CurrentRegion

I'm trying to write a small piece of VBA that will shade a column of excel cells based on RGB variables in adjacent columns. So I have a table of data (no headings) with 3 columns (R G and B) and x rows (amount will vary). What I would like is to colour a 4th column to the right of this table in a colour based on the 3 numbers to the left. Note that I plan to select the top left cell each time I perform this.

Below is the code I have used but I get an error message:

Error 438... Object doesnt support this property or method

pointing to the Set rng line as being the problem.

Any thoughts or help much appreicated

Sub RGBTest()

Dim rng As Range
Dim n As Integer

Set rng = ActiveSheet.ActiveCell.CurrentRegion

ActiveCell.Offset(0, 3).Activate

For n = 1 To rng.Rows.Count

ActiveCell.Interior.Color = RGB(rng.Cells(n, 1), rng.Cells(n, 2), rng.Cells(n, 3))

ActiveCell.Offset(1, 0).Activate
Next n

End Sub

Upvotes: 1

Views: 1359

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

So the line

Set rng = ActiveSheet.ActiveCell.CurrentRegion

causes the error Error 438... Object doesnt support this property or method.

That means either ActiveSheet doesn't support .ActiveCell or ActiveCell doesn't support .CurrentRegion.

ActiveCell is a range and CurrentRegion is a property of Range so that shouldn't be it.

However, ActiveCell is not a property of a worksheet but of Application. That makes sense since there is only one active cell per Excel instance, not per sheet. So instead of 

Set rng = ActiveSheet.ActiveCell.CurrentRegion

just use 

Set rng = ActiveCell.CurrentRegion

Upvotes: 1

Related Questions