Solved: to extend the coloring

[Yjmmay34]

hello, now the color is until column H, i need to extend the color until column Z, as i got a loy of sheets like this, so i need a macro to do this for me. Is it possible? thank you!

[GTO]

Not well tested, and not utterly sure that the .UsedRange would faithfully pick up interior, but it is what I could think of...


Sub exa()
Dim wks As Worksheet
Dim rngColor As Range
Dim rCell As Range

For Each wks In ThisWorkbook.Worksheets

Set rngColor = wks.Range(wks.Cells(1, 1), _
wks.Cells(wks.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))

For Each rCell In rngColor
If Not rCell.Interior.ColorIndex = xlColorIndexNone Then
rCell.Resize(, 26).Interior.ColorIndex = rCell.Interior.ColorIndex
End If
Next
Next
End Sub

Mark

[Yjmmay34]

hi, gto.
thank you for the help. But i got my own loop to run through every sheet i need, so i need to insert your code to my loop, so i edit your code to


'Dim wks As Worksheet
Dim rngColor As Range
Dim rCell As Range

' For Each wks In ThisWorkbook.Worksheets

' Set rngColor = wks.Range(wks.Cells(1, 1), _
wks.Cells(wks.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))
Set rngColor = Range(Cells(1, 1), Cells(UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))
For Each rCell In rngColor
If Not rCell.Interior.ColorIndex = xlColorIndexNone Then
rCell.Resize(, 26).Interior.ColorIndex = rCell.Interior.ColorIndex
End If
Next
' Next
i changed it lthis way, dont know where is the problem, got error says "Object required". Can help with this?? Thank you!

[Aussiebear]

You have set rngcolor twice. Which one do you need?

[GTO]

I see that you have the first Set REM'd. Why is the active Set not qualified?

[mdmackillop]

I see that you have the first Set REM'd. Why is the active Set not qualified?

There is also a linebreak in that line, so it is part REM'D.

I don't follow the explanation.

[GTO]

Hi Malcom,


There is also a linebreak in that line, so it is part REM'D.

But there's a line continuation character as well; just not picked up by the tags.


I don't follow the explanation.

I simply questioned (or more accurately, was trying to question) this bit:

Set rngColor = Range(Cells(1, 1), Cells(UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))

W/O seeing how the code is incorporated into the loop, it is of course a guess, but I was suspecting that Range/Cells/UsedRange need qualified. My bad, I was certainly not very clear.

Mark

[mdmackillop]

Hi Mark,
It was the OP I was failing to follow!

[GTO]

ACK!:doh:

Sorry my friend, still blonde and a long shift...

[Yjmmay34]

Hi, GTO. sorry for making you all confused. maybe you could just remove the sheets loop part for me, so that i could just insert your code into my loop.......Thank you

[GTO]

Hi, GTO. sorry for making you all confused. maybe you could just remove the sheets loop part for me, so that i could just insert your code into my loop.......Thank you

No problems, by that point in the day, most anything could throw me.

I suspect the problem is this line:

Set rngColor = Range(Cells(1, 1), Cells(UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))


I think it probably needs to be:

Set rngColor = SomeSheet.Range( _
SomeSheet.Cells(1, 1), _
SomeSheet.Cells(SomeSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1) _
)

Where 'SomeSheet' is a sheet that is referenced.



If this is not clear, I would suggest showing us your current loop.


Mark

[Yjmmay34]

hello, gto, the macro can work already, thank you all.