Insert cell .Address as string into Range() ?

Question

I have a piece of code that should loop through each cell in a Range. Under certain conditions those cell Addresses (e.g., "E8") should be added to an array, and each cell in that range should be changed to the "Good" style.

However when I run this, I get the error:

Run-time error '1004':

Method 'Style' of object 'Range' failed

Here is my code:

Dim cell As Range

Dim cellArray() As Variant

For Each cell In Range("A8:H8")

   If (IsEmpty(cell.Value)) And (cell.Style <> "Bad") Then

      ReDim cellArray(cell + 1)

      cellArray(cell) = cell.Address(False, False)

      Range(cell.Address(False, False)).Style = "Good"

   End If

Next cell

If I try to print Range(cell.Address(False, False)) to a MsgBox, it comes back blank. I also tried assigning Range(cell.Address(False, False)) to a variable "r" which I dimensioned as both a String and a Range, but both times there were other errors.

Any help appreciated.

Answer 1

As soon as I responded I tried unprotecting the sheet with

Sheets("Worksheet1").Unprotect

That was the issue!

Hi Kate,

As Andreas said, there had to be another issue because I had tested the code that I posted and I now am happy for you that you have found the problem that prevented it from working.

Would you like to now open the thread and mark the posts that helped to resolve your issue. You can mark more than one.

Answer 2

I was just using "Good" as a test (since I haven't really found a lot of documentation on specific styles other than Good or Bad) to see if I could even get the cells to change color at all, which I still unfortunately can't since

Add a new sheet in your file, copy the code below into a regular module and run it.

It lists the available styles in the file and some of the properties.

Andreas.

Sub Test()

  Dim S As Style

  Dim i As Long, j As Long

  Dim Arr, Prop, Item

  Prop = Array("Name", "BuiltIn", "AddIndent", "FormulaHidden", "IncludeAlignment", _

    "IncludeBorder", "IncludeFont", "IncludeNumber", "IncludePatterns", _

    "IncludeProtection", "Locked", "NumberFormat")

  i = i + 1

  Range("A" & i).Resize(, UBound(Prop) + 1).Value = Prop

  For Each S In ThisWorkbook.Styles

    Arr = Prop

    j = 0

    For Each Item In Arr

      Arr(j) = CallByName(S, Item, VbGet)

      j = j + 1

    Next

    i = i + 1

    Range("A" & i).Resize(, UBound(Prop) + 1).Value = Arr

  Next

End Sub