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!
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!
Not well tested, and not utterly sure that the .UsedRange would faithfully pick up interior, but it is what I could think of...
MarkSub 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
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
[VBA]
'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
[/VBA]i changed it lthis way, dont know where is the problem, got error says "Object required". Can help with this?? Thank you!
You have set rngcolor twice. Which one do you need?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.Originally Posted by GTO
I don't follow the explanation.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi Malcom,
But there's a line continuation character as well; just not picked up by the tags.Originally Posted by mdmackillop
I simply questioned (or more accurately, was trying to question) this bit:Originally Posted by mdmackillop
[vba]Set rngColor = Range(Cells(1, 1), Cells(UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1)) [/vba]
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
Hi Mark,
It was the OP I was failing to follow!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
ACK!
Sorry my friend, still blonde and a long shift...
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.Originally Posted by Yjmmay34
I suspect the problem is this line:
[vba]
Set rngColor = Range(Cells(1, 1), Cells(UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1))
[/vba]
I think it probably needs to be:
Where 'SomeSheet' is a sheet that is referenced.Set rngColor = SomeSheet.Range( _ SomeSheet.Cells(1, 1), _ SomeSheet.Cells(SomeSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 1) _ )
If this is not clear, I would suggest showing us your current loop.
Mark
hello, gto, the macro can work already, thank you all.