How do I find all the cells in a column (or even a worksheet) that are colored lavender & clear the contents of only those cells?
Thanks.
How do I find all the cells in a column (or even a worksheet) that are colored lavender & clear the contents of only those cells?
Thanks.
Assuming your cells contain constants (that you do not want to delete formulas):
Sub Test1()
Dim LR As Long, LC As Integer, cell As Range
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For Each cell In Range(Cells(1, 1), Cells(LR, LC)).SpecialCells(2, 3)
With cell
If .Interior.ColorIndex = 39 Then .ClearContents
End With
Next cell
End Sub
This avoids the unreliable UsedRange and SpecialCells LastCell range references. Also assumes by "lavender" you are referring to index #39.
Tom-
Works perfect. Thank you very much.
Just so you know guys. From Excel 2000 (I think) ownwards, the Edit>Find has a Find Format option. This could be used to speed up the process.
Don’t have an account yet? Register yourself now and be a part of our community!