Unlock based on cell colour

Hi

I have a spreadsheet with input cells coloured blue. I need a macro to 
unlock all blue cells. I know I need to use Colorindex but I can't work out 
how to put it all together.

Many thanks
0
Utf
4/26/2010 1:10:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
866 Views

Similar Articles

[PageSpeed] 29

Something like this.

With Sheets("Sheet1")
..Unprotect
For Each c In .Range("A2:F100")
      If c.ColorIndex = 5 Then
          c.Locked = False
      End If
Next
..Protect
End With

"violasrbest" <violasrbest@discussions.microsoft.com> wrote in message 
news:CDBE053D-66C2-46C7-B66C-85EEE3FF0918@microsoft.com...
> Hi
>
> I have a spreadsheet with input cells coloured blue. I need a macro to
> unlock all blue cells. I know I need to use Colorindex but I can't work 
> out
> how to put it all together.
>
> Many thanks 


0
JLGWhiz
4/26/2010 1:40:28 PM
Here is a non-looping method that should be very fast...

Sub FindRedFont()
  Application.FindFormat.Interior.ColorIndex = 5
  Application.FindFormat.Locked = True
  Application.ReplaceFormat.Locked = False
  Worksheets("Sheet1").Cells.Replace "", "", _
    SearchFormat:=True, ReplaceFormat:=True
End Sub

Just change the ColorIndex value of 5 which I used in the first statement 
above to the actual ColorIndex of the cells you want to unlock and change 
the Worksheet name from "Sheet1" which I used in the last (continued) line 
above to the actual name for the Worksheet with your "blue" cells on them.

-- 
Rick (MVP - Excel)



"violasrbest" <violasrbest@discussions.microsoft.com> wrote in message 
news:CDBE053D-66C2-46C7-B66C-85EEE3FF0918@microsoft.com...
> Hi
>
> I have a spreadsheet with input cells coloured blue. I need a macro to
> unlock all blue cells. I know I need to use Colorindex but I can't work 
> out
> how to put it all together.
>
> Many thanks 

0
Rick
4/26/2010 2:00:00 PM
Ignore the macro name I used... I cannibalized another routine I had laying 
around to make the macro I posted for you and forgot to change the original 
macro's name. Here is the same code but with a more apt macro name (which 
you can change to suit your preferences, of course)...

Sub UnlockBlueCells()
  Application.FindFormat.Interior.ColorIndex = 5
  Application.FindFormat.Locked = True
  Application.ReplaceFormat.Locked = False
  Worksheets("Sheet1").Cells.Replace "", "", _
    SearchFormat:=True, ReplaceFormat:=True
End Sub

-- 
Rick (MVP - Excel)



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:#ZRaPjU5KHA.6052@TK2MSFTNGP02.phx.gbl...
> Here is a non-looping method that should be very fast...
>
> Sub FindRedFont()
>  Application.FindFormat.Interior.ColorIndex = 5
>  Application.FindFormat.Locked = True
>  Application.ReplaceFormat.Locked = False
>  Worksheets("Sheet1").Cells.Replace "", "", _
>    SearchFormat:=True, ReplaceFormat:=True
> End Sub
>
> Just change the ColorIndex value of 5 which I used in the first statement 
> above to the actual ColorIndex of the cells you want to unlock and change 
> the Worksheet name from "Sheet1" which I used in the last (continued) line 
> above to the actual name for the Worksheet with your "blue" cells on them.
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "violasrbest" <violasrbest@discussions.microsoft.com> wrote in message 
> news:CDBE053D-66C2-46C7-B66C-85EEE3FF0918@microsoft.com...
>> Hi
>>
>> I have a spreadsheet with input cells coloured blue. I need a macro to
>> unlock all blue cells. I know I need to use Colorindex but I can't work 
>> out
>> how to put it all together.
>>
>> Many thanks
> 
0
Rick
4/26/2010 2:09:31 PM
Thanks Rick, works like lightning

"Rick Rothstein" wrote:

> Ignore the macro name I used... I cannibalized another routine I had laying 
> around to make the macro I posted for you and forgot to change the original 
> macro's name. Here is the same code but with a more apt macro name (which 
> you can change to suit your preferences, of course)...
> 
> Sub UnlockBlueCells()
>   Application.FindFormat.Interior.ColorIndex = 5
>   Application.FindFormat.Locked = True
>   Application.ReplaceFormat.Locked = False
>   Worksheets("Sheet1").Cells.Replace "", "", _
>     SearchFormat:=True, ReplaceFormat:=True
> End Sub
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> 
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:#ZRaPjU5KHA.6052@TK2MSFTNGP02.phx.gbl...
> > Here is a non-looping method that should be very fast...
> >
> > Sub FindRedFont()
> >  Application.FindFormat.Interior.ColorIndex = 5
> >  Application.FindFormat.Locked = True
> >  Application.ReplaceFormat.Locked = False
> >  Worksheets("Sheet1").Cells.Replace "", "", _
> >    SearchFormat:=True, ReplaceFormat:=True
> > End Sub
> >
> > Just change the ColorIndex value of 5 which I used in the first statement 
> > above to the actual ColorIndex of the cells you want to unlock and change 
> > the Worksheet name from "Sheet1" which I used in the last (continued) line 
> > above to the actual name for the Worksheet with your "blue" cells on them.
> >
> > -- 
> > Rick (MVP - Excel)
> >
> >
> >
> > "violasrbest" <violasrbest@discussions.microsoft.com> wrote in message 
> > news:CDBE053D-66C2-46C7-B66C-85EEE3FF0918@microsoft.com...
> >> Hi
> >>
> >> I have a spreadsheet with input cells coloured blue. I need a macro to
> >> unlock all blue cells. I know I need to use Colorindex but I can't work 
> >> out
> >> how to put it all together.
> >>
> >> Many thanks
> > 
> .
> 
0
Utf
4/26/2010 3:51:02 PM
Reply:

Similar Artilces:

When printing using Excel the text does not fit in the cell
The text fits in a cell on the screen, but when I print part of it is omitted because the cell box doesn't seem to be large enough. I can add a blank line by using Alt-enter, but sometimes I need to add two lines. My text varies from a few words to as many as 30. There has got to be a better way than trying to guess if an additional line is needed an add it manually. Sometimes worksheets have over 300 lines!! Have you set the alignment for these cells to Wrap Text and the row height to Auto? On Fri, 25 Mar 2005 07:33:04 -0800, "MSUbully" <MSUbully@discussions.micros...

Filter Data based on Form
Hi All, I have a form that is based on a query. I also have a unbound list box that the user can select a location from. The Locations are Seattle, Bellevue and Redmond. When the user selects a location I would like the form to only display the records with those locations. This is a Single Form not a datasheet view and this is not a subform. Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1 I would use the listbox onclick event for this. Dim strSQL as String strSQL ...

Server and Client based Rules in Outlook
HI, I created both Server and Client based rules in Outlook to respond to emails. The client based one is "if an email comes from a specific email, respond with a specified template". The server based one is "If an email comes from a specific email, respond with a message from the server". My issue is that these rules will only respond once to an email address, is there a way to have the rules respond to an email address no matter how many times it comes in? We can't use out of office assistant because those responses have been blocked from going ...

Resize Cells
How do I get the row or cell to automatically enlarge with wrappe text? Thanks, J -- J9K Use Row >> Autofit. Regards. ...

please help me link cells that have numbers and letters
Can anyone help me learn how I can type a stock number on sheet 1 in m workbook that contains both letters and numbers and have i automatically show up in a dedicated cell on sheet 2. I can do thi with numbers only, but I heard it can be done. Any help on this matte is greatly appriciated -- thuma100 ----------------------------------------------------------------------- thuma1004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1481 View this thread: http://www.excelforum.com/showthread.php?threadid=26439 Hi not really sure what you're trying to do. What...

knowlege base article 289815
How can i download this fix q289815.exe to solve the following problem Visio2000: Cross-Functional Flowchart Shapes Incorrectly Aligned When You Open Drawing Efe, The bottom of that article indicates that you need to contact PSS: "To resolve this problem, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS" -- Dawn This posting is provided "AS IS&qu...

Edit Cell Macro
I need to create a macro to edit the contents of a cell and put quotes around it. For example: car ---> "car" The contents of the cells will be both numbers and words. I would need to use this format on everything in the spreadsheet in order to import this into another program. Thanks, Tyler tmclean79 at shaw dot ca Tyler One way. Create a button and assign the code below. Sub Button1_Click() whatsthere = ActiveCell.Text ActiveCell.Clear ActiveCell.Value = Chr(34) + whatsthere + Chr(34) End Sub select a cell and click the button. Making it work on multiple ce...

Merge Cell Button Greyed Out
Hello all. Someone has been futzing with my spreadsheet and has managed to break it. I've got 6 cells (3 cells in 2 rows) I want to merge. They were merged once, but as I say, someone has been playing. Anyway, I'm trying to merge these 6 cells in a worksheet and the merge cell button is greyed out. I can merge other cells in the sheet, but none of these 6 in any combination. Any ideas as to how I can get these cells to merge, or un-do whatever has been done that is preventing this, would be apprecaited. Thanks all! -- craig-o -----------------------------------------------...

text colour
I notice when I reply to a message it gives me blue text but when I send it gives me black text. Can someone tell me how to keep it on black normal text or another better colour when I reply to messages. thanks Bacchus, you wrote on Fri, 23 Sep 2005 01:27:45 -0400: > I notice when I reply to a message it gives me blue text but when I send it > gives me black text. Can someone tell me how to keep it on black normal > text or another better colour when I reply to messages. Tools | Options | Mail-Format | Fonts -- Best Regards Christian Goeller Some misspellings, grammatica...

Text file to Google Base
Google requires that files bulk-oploaded to Google Base are formatted as either .txt or .xml. I have tried the first but so far with no success. One issue that might tease me is that Google require the file to be in Latin1 or UTF-8 coding. But how do I achieve this? As far as I can see this is not controllable when choosing a "Save as" "Text (tab delimited) (*.txt)" and in XP's regional settings there is no Latin1 or UTF-8. The error message I get from Google is not very helpful, it only says that I must choose the right format. I have tried to download a Google exam...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

Last Active Cell
I have a template created that pulls information from different worksheets. I would like to be able to add "End of Report" to the bottom of the report. I have visited different websites and found how to find the last active cell but it does not work for me because of formulas that were written in the cells. I tried to copy and paste special, but I still end up going beyond where the last cell actually is. For example in column A1 I have ='Sheet1'!A1. Now I have this copied down to A100. If I end up only going to A50, my last active cell formula still takes me ...

Restrict data entry in a cell
Hi, I would like to ensure that a user can only input one of the following numbers in a cell: 1, 2, 3, 4, 5, 6, 7 or 10 I'm using Excel 2003. Thank you Ephraim Data/Validation select List enter: 1,2,3,4,5,6,7,10 Click OK. You're done. Bob Umlas Excel MVP "Ephraim" <rick.malone@gmail.com> wrote in message news:b26e77d7-f5f6-4d44-ad39-813e357e1a7e@g4g2000yqa.googlegroups.com... > Hi, > I would like to ensure that a user can only input one of the following > numbers in a cell: > 1, 2, 3, 4, 5, 6, 7 or 10 > > I'm using Exc...

insert image to the cells
how can I insert image to the cell, preferably from the clipboard? when I paste from the image in the windows clipboard, the image is layered. I mean the image is not in the right cell. how can I insert the image into the CELL? You can't - cells can contain only values or formulae. Images/other Shape objects always exist on the drawing layer "above" the cell grid. You can position and size the image, and set its properties to Move and Size with cells. In article <#rA832h2HHA.5772@TK2MSFTNGP02.phx.gbl>, kang <kang@hao.com> wrote: > how can I insert image to ...

UnLocked Cells No Color
Hi there, I have a spread sheet which is passed around frequently. Therefore I have locked a number of cells to protect its information. However, its necesary that other change the color of the unlocked cells. The problem is that once the sheet is protected the unlocked (and therefore changeable) do not have any color icon and so I cant change them. I cant figure out a way to have locked cells and still allow color changes on the unlocked without giving the password away (which of course defeats the purpose) Cheers! This capability was added in xl2002. But if you're running an earlier...

Pasting into visible cells only
I have two worksheets that are set up to custom view with a sort function; i.e. it hides lines. I know how to copy visible lines to a fully open worksheet. If the worksheet I want to copy to is also sorted to have the same number of visible lines is there a way to copy just into those visible lines? I get an error box saying " cannot paste in to multiple selections". Excel will only paste into a contiguous range, so you can't copy visible cells only, and paste into visible cells only. TV Man wrote: > I have two worksheets that are set up to custom view with > a so...

Hiding Rows Based on Percentile
Hello, I have recently searched for help on the Percentile function, and found a lot of what I was looking for. (at least I now understand it). However, now that I have calculated the percentile on my sheet, I want to filer the list of rows so that only those in the nth percentile are shown on the sheet. How would I go about doing this ? (I'm assuming VBA code would need to be used, but I'm not that great with it yet) Cheers. J. Numbers of users go the "helper-column" route, that is Insert a new column (which you can later hide) and enter in say cell G1 an If() statemen...

Nothing in cell only in formula bar
Why is it (2007) that sometimes when I type a formula (or anything for that matter) into a cell nothing shows up in the cell, but it will show up in the formula bar? Do you use multiple monitors? JimS wrote: > > Why is it (2007) that sometimes when I type a formula (or anything for > that matter) into a cell nothing shows up in the cell, but it will > show up in the formula bar? -- Dave Peterson No. I'm on a laptop. On Tue, 07 Oct 2008 11:46:23 -0500, Dave Peterson <petersod@verizonXSPAM.net> wrote: >Do you use multiple monitors? > >JimS wrote: >>...

Need help with copying data from cells
I currently need to get the data from an entire column of cells, and add them to column. The thing is, the data in the column is not a straight line, there are blank cells in between data entrys in the column. Basically, i need to copy the data values only from the cells that contain data, where it is put in one neat straight line in another column. Hopefully this makes sense... :confused: -- paulket ------------------------------------------------------------------------ paulket's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24742 View this thread: ht...

Create an index based on multiple criteria
Ok...here's what I'm trying to acheive: I am trying to create a form which will calculate who's signature is required based on several criteria. The three cases for the signatures are: District Sales Manager (DSM) only DSM & Managing Director (MD) DSM, MD, & Sales Director (SD) There are three factors used to determine the required signatures. So, there are three text boxes on my form. I have named those controls as follows: txtCM (Contribution Margin) [intCM] txtRV (Revenue) [intRV] txtCst (Cost) [intCST] And here are the different scenarios: 1. If intCM > 30 and i...

function based on result of same function
When using an extensively long formula is it possible to use some function that is the result of that, without writing or pasting the whole formula again? For instance: SUM (...)*A3/.1*c3 if this were to equal $100 and then I wanted to subract 9.75% of that same result, to then equal $100 - (100*.0975) or 90.25 (SUM (...)*A3/.1*c3)*(1-9.75%) -- Kind Regards, Niek Otten Microsoft MVP - Excel "GolfGal" <golfgal@noemail.com> wrote in message news:1632ACBC-4CEE-45E8-B1A9-6A934E13A440@microsoft.com... > When using an extensively long formula is it possible to use som...

Save spreadsheet using cell values as filename
I am trying to use a command button to save a spreadsheet using cell values as the file name from the tab Master. For example I want to use cells B2 (Tony.Dungate) and F2 (2010.March) to create a file name of Tony.Dungate.2010.March.xls. Essentialy I would need something like : Master!B2&.&Master!G2&".xls" Can someone advise me on the correct code to do this. I have been trying to use the following without success: Const sRoot As String = "V:\Database Logs\" With ActiveWorkbook .SaveAs Filename:=sRoot & _ ....

Counting days and text in cells
I'm trying to create statistics from a sheet with order data from my webshop. Each row consists of an order with: Column A = order number Column B = Payment option Column C = Order date Example: 1 PayPal 010110 2 VISA 010110 3 PayPal 020110 4 PayPal 020110 5 PayPal 020110 6 AMEX 030110 I'm interested to count how many orders have been placed each day during a month and display an average based on the dates in column C. That is, 1st of January = 2, 2nd of January = 3 etc. So for the three days above the average number of orders/day...

Automatic avoiding empty cell
Hi, I am making templates to draw chart (scatter charts and bar charts). Scatter charts also have trendlines in them. At times I dont have value for some of the items which are used to make these (scatter)charts. This doesnot give a correct trendline since it assumes value as zero. As such I have to manually change the data source and exclude items for which i donot have a value. Is there a solution such that the excel automaticallty doesnot take the item (which doesnot have a value) to draw the chart. e.g. Margin Company A 14% Company B 18% Company C 9% Company D N/A or 0...

how do i unlock modifications
My Word 2007 used to work. Now I can't do ANYTHING on it because it tells me the modification I am trying to use is locked. How do I unlock it? Are you using a trial version of Word that has passed its use by date? If so, to see how to convert it to a regular version, see "How do I convert my trial?" at: http://us20.trymicrosoftoffice.com/faq.aspx Another possibility is that you have not Activated the software. You can also find out how to do that at the above site. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my ...