Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell shading
Is there a way to automatically reset the value of all cells in a spreasheet
that have been shaded a specific colour? In my spreadsheet I have (for ease of use) shaded yellow all the cells that the user can input data into. Anything not shaded yellow is protected and unavailable to the user. What i want to do is clear all the user inputs at the end of each month, so that at the start of the following month they can re-use the spreadsheet. Can this be done? i could not find anything in VBA, but there must be something. Any help is appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell shading
Sub clearum()
Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.Interior.ColorIndex = 6 Then cell.ClearContents End If Next End Sub The 6 is for yellow. Adapt as you like. -- Gary''s Student - gsnu200806 "dunny_budgie" wrote: Is there a way to automatically reset the value of all cells in a spreasheet that have been shaded a specific colour? In my spreadsheet I have (for ease of use) shaded yellow all the cells that the user can input data into. Anything not shaded yellow is protected and unavailable to the user. What i want to do is clear all the user inputs at the end of each month, so that at the start of the following month they can re-use the spreadsheet. Can this be done? i could not find anything in VBA, but there must be something. Any help is appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell shading
In xl2002+, you can use edit|replace to clear the cells that have a common
formatting. If you're using an earlier version of excel, then I would use: Select the cells that need to be cleared Use Insert|Name|Insert (xl2003 menus) to create a name for this selected range. Then whenever I wanted to clear that range, I could select the range (ctrl-g and type the name) and then hit the delete key (on the keyboard). dunny_budgie wrote: Is there a way to automatically reset the value of all cells in a spreasheet that have been shaded a specific colour? In my spreadsheet I have (for ease of use) shaded yellow all the cells that the user can input data into. Anything not shaded yellow is protected and unavailable to the user. What i want to do is clear all the user inputs at the end of each month, so that at the start of the following month they can re-use the spreadsheet. Can this be done? i could not find anything in VBA, but there must be something. Any help is appreciated! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell shading
Try this macro
Sub ClearColoredCells() Dim c As Range, ClrVal Application.InputBox("Select one of the cells to be cleared", Type:=8).Select ClrVal = Selection.Interior.ColorIndex ActiveSheet.UsedRange.Select For Each c In Selection If c.Interior.ColorIndex = ClrVal Then c.ClearContents End If Next c End Sub It pauses & asks you to select any one of the shaded cells to be cleared. Then it clears all the cells with that same interior fill color. Hope this helps, Hutch "dunny_budgie" wrote: Is there a way to automatically reset the value of all cells in a spreasheet that have been shaded a specific colour? In my spreadsheet I have (for ease of use) shaded yellow all the cells that the user can input data into. Anything not shaded yellow is protected and unavailable to the user. What i want to do is clear all the user inputs at the end of each month, so that at the start of the following month they can re-use the spreadsheet. Can this be done? i could not find anything in VBA, but there must be something. Any help is appreciated! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell shading
There are a couple ways:
1. Just save a copy of the file, empty and then replace the sheet that gets the input each month. 2. Create a macro that does something like this: Note: if the cell color is not the "6" listed below, that # would need to be adjusted. to test it, record a macro and adjust the same color that is in a yellow cell. Then see what value it used. Sub ClearYellowCells() Dim X As Long Dim Y As Long Dim LastRow As Long Dim LastCol As Long ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Activate Let LastRow = ActiveCell.Row Let LastCol = ActiveCell.Column For X = 1 To LastRow For Y = 1 To LastCol If Cells(X, Y).Interior.ColorIndex = 6 Then Cells(X, Y).Value = Empty End If Next Next MsgBox ("done resetting all yellow cells!") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to sort the data by cell shading or cell color? | Excel Discussion (Misc queries) | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Cell Shading | Excel Discussion (Misc queries) | |||
shading a rowwhen a time is entered but no shading when 0 is enter | Excel Worksheet Functions | |||
change cell shading whenever contents different from previous cell | Excel Discussion (Misc queries) |