|
Clear Shaded Cells
Is there a way to clear all shaded cells in a worksheet using a macro?
I have received a lot of help from this news group. Thank you very much. |
Clear Shaded Cells
Sub tryme()
For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. |
Clear Shaded Cells
Or just get the entire range at once:
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson |
Clear Shaded Cells
Thank you for the quick response.
I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson |
Clear Shaded Cells
Option Explicit
Sub testme() Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.Interior.ColorIndex = 3 Then myCell.ClearContents End If Next myCell End Sub Record a macro when you change the colorindex of a cell. Then use that in place of the 3. Select the range you want before you run the code. Or use the UsedRange if you want. Ed Davis wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
For Each mycell In ActiveSheet.UsedRange
If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson |
Clear Shaded Cells
This is what I get when I record the macro:
I tried to use xlAutomatic Did not work I tried to use 3 did not work Then I tried to use 0.599993896298105 and that did not work either. I even tried to use the 0 and that did not work either. Range("B17").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 End With "Dave Peterson" wrote in message ... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.Interior.ColorIndex = 3 Then myCell.ClearContents End If Next myCell End Sub Record a macro when you change the colorindex of a cell. Then use that in place of the 3. Select the range you want before you run the code. Or use the UsedRange if you want. Ed Davis wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
I get cannot clear part of a merged cell. with this code
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson |
Clear Shaded Cells
I get the following error
cannot clear part of a merged cell. I only had 1 merged cell that was shaded and even after removing the merge and the shade I still get the same error. Can anyone tell me if there is a way around this. "Ed Davis" wrote in message ... I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson |
Clear Shaded Cells
Dim myCell As Range
Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell Ed Davis wrote: I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
Hi Dave
I copied and pasted this and absolutely nothing happens no errors and nothing changed on the sheet Thanks for your help Dave. Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell "Dave Peterson" wrote in message ... Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell Ed Davis wrote: I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
Did you select a range before running the macro?
If you don't want to select a range go back to Activesheet.usedrange instead of selection. Gord On Tue, 16 Jun 2009 12:43:06 -0400, "Ed Davis" wrote: Hi Dave I copied and pasted this and absolutely nothing happens no errors and nothing changed on the sheet Thanks for your help Dave. Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell "Dave Peterson" wrote in message ... Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell Ed Davis wrote: I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
I got it to work using this suggestion.
Thank you very much. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Did you select a range before running the macro? If you don't want to select a range go back to Activesheet.usedrange instead of selection. Gord On Tue, 16 Jun 2009 12:43:06 -0400, "Ed Davis" wrote: Hi Dave I copied and pasted this and absolutely nothing happens no errors and nothing changed on the sheet Thanks for your help Dave. Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell "Dave Peterson" wrote in message ... Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell Ed Davis wrote: I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
Clear Shaded Cells
Good to hear.
Gord On Tue, 16 Jun 2009 15:14:43 -0400, "Ed Davis" wrote: I got it to work using this suggestion. Thank you very much. "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Did you select a range before running the macro? If you don't want to select a range go back to Activesheet.usedrange instead of selection. Gord On Tue, 16 Jun 2009 12:43:06 -0400, "Ed Davis" wrote: Hi Dave I copied and pasted this and absolutely nothing happens no errors and nothing changed on the sheet Thanks for your help Dave. Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell "Dave Peterson" wrote in message ... Dim myCell As Range Dim myRng As Range Set myRng = Selection 'ActiveSheet.usedrange For Each myCell In myRng.Cells If myCell.MergeArea.Cells(1).Interior.ColorIndex < xlNone Then myCell.Value = "" End If Next myCell Ed Davis wrote: I get cannot clear part of a merged cell. with this code "Gord Dibben" <gorddibbATshawDOTca wrote in message ... For Each mycell In ActiveSheet.UsedRange If mycell.Interior.ColorIndex < xlNone Then mycell.ClearContents End If Next mycell Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 08:24:43 -0400, "Ed Davis" wrote: Thank you for the quick response. I was looking to only clear the data not the shade as the shaded areas show the user where the data goes. "Dave Peterson" wrote in message ... Or just get the entire range at once: ActiveSheet.UsedRange.Interior.ColorIndex = xlNone or even do all the cells: activesheet.cells.Interior.ColorIndex = xlNone Bernard Liengme wrote: Sub tryme() For Each mycell In ActiveSheet.UsedRange mycell.Interior.ColorIndex = xlNone Next mycell End Sub If you are new to VBA: David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ed Davis" wrote in message ... Is there a way to clear all shaded cells in a worksheet using a macro? I have received a lot of help from this news group. Thank you very much. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 03:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com