Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
I create a workbook with just one sheet, and make sure that
range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
Stuart,
You are processing the columns, not the cells. Try this Sub Test_Protection() Dim ws As Worksheet, c As Range Dim cell As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") For Each cell In c.Cells With cell If Not cell.Locked = True Then 'User is permitted to edit this cell, so color it: cell.Interior.ColorIndex = 34 End If End With Next cell Next c End With Next End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
It appears that when you specify the columns in the .UsedRange property
it returns the entire used range in the column to your c variable. Then the checking for locked will not work (since the entire range i not locked). You will need to specify your range in a different manor Something like: Dim BigRange as Range Set BigRange = Range("A1:G" & Range("G65536").End(xlUp).Row -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
Don't know if this would help, but in later versions of Excel, the following
appears to work... without a loop. Sub Demo() Application.FindFormat.Locked = False Application.ReplaceFormat.Interior.ColorIndex = 4 Columns("A:G").Replace _ What:=vbNullString, _ Replacement:=vbNullString, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=True, _ ReplaceFormat:=True End Sub HTH :) Dana DeLouis "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
Many thanks to you both. I see what you mean.
One further question please: When individual cells are coloured it's not so bad that Excel's default gridlines are obliterated.........but when a large portion of the screen is coloured, it's difficult to orientate without those gridlines. How do I get the gridlines back for the cells that change colour .....or, would it be quicker to set the gridlines for the usedrange once the colour-changing is complete? Regards. "Bob Phillips" wrote in message ... Stuart, You are processing the columns, not the cells. Try this Sub Test_Protection() Dim ws As Worksheet, c As Range Dim cell As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") For Each cell In c.Cells With cell If Not cell.Locked = True Then 'User is permitted to edit this cell, so color it: cell.Interior.ColorIndex = 34 End If End With Next cell Next c End With Next End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
You have to use borders.
-- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. I see what you mean. One further question please: When individual cells are coloured it's not so bad that Excel's default gridlines are obliterated.........but when a large portion of the screen is coloured, it's difficult to orientate without those gridlines. How do I get the gridlines back for the cells that change colour ....or, would it be quicker to set the gridlines for the usedrange once the colour-changing is complete? Regards. "Bob Phillips" wrote in message ... Stuart, You are processing the columns, not the cells. Try this Sub Test_Protection() Dim ws As Worksheet, c As Range Dim cell As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") For Each cell In c.Cells With cell If Not cell.Locked = True Then 'User is permitted to edit this cell, so color it: cell.Interior.ColorIndex = 34 End If End With Next cell Next c End With Next End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
Many thanks but it would appear not for me!
Using Excel 2000 and Win2k. I get this error on the line: Application.FindFormat.Locked = False Run time error 438 Object doesn't support this property or method I had begun to wonder if I could use the statement .EnableSelection = xlUnlockedCells in some way, since this seems to indicate a way to 'globally' identify the unlocked cells, rather than a cell by cell approach. Regards. "Dana DeLouis" wrote in message ... Don't know if this would help, but in later versions of Excel, the following appears to work... without a loop. Sub Demo() Application.FindFormat.Locked = False Application.ReplaceFormat.Interior.ColorIndex = 4 Columns("A:G").Replace _ What:=vbNullString, _ Replacement:=vbNullString, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=True, _ ReplaceFormat:=True End Sub HTH :) Dana DeLouis "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to colour Unlocked cells
Thanks and Regards.
"Tom Ogilvy" wrote in message ... You have to use borders. -- Regards, Tom Ogilvy "Stuart" wrote in message ... Many thanks to you both. I see what you mean. One further question please: When individual cells are coloured it's not so bad that Excel's default gridlines are obliterated.........but when a large portion of the screen is coloured, it's difficult to orientate without those gridlines. How do I get the gridlines back for the cells that change colour ....or, would it be quicker to set the gridlines for the usedrange once the colour-changing is complete? Regards. "Bob Phillips" wrote in message ... Stuart, You are processing the columns, not the cells. Try this Sub Test_Protection() Dim ws As Worksheet, c As Range Dim cell As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") For Each cell In c.Cells With cell If Not cell.Locked = True Then 'User is permitted to edit this cell, so color it: cell.Interior.ColorIndex = 34 End If End With Next cell Next c End With Next End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... I create a workbook with just one sheet, and make sure that range("A1") is not Locked. With this book as the activeworkbook, I run this code: Sub Test_Protection() Dim ws As Worksheet, c As Range For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect For Each c In .UsedRange.Columns("A:G") With c If Not c.Locked = True Then 'User is permitted to edit this cell, so color it: c.Interior.ColorIndex = 34 End If If Not .Range("A1").Locked = True Then .Range("A1").Interior.ColorIndex = 34 End If End With Next End With Next End Sub In this example, the 'c' code does not recognise that "A1" is not Locked, but the "A1" code does, and the cell is coloured correctly. Why is the line 'If Not c.Locked = True Then' failing please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to automatically colour cells depending on text? | Excel Discussion (Misc queries) | |||
How to change font colour in unlocked cell of protected sheet | Excel Discussion (Misc queries) | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
Moving from unlocked cells to unlocked cells in an excel form | Excel Worksheet Functions | |||
Tabbing through Unlocked cells | Excel Worksheet Functions |