| Home |
| Search |
| Today's Posts |
|
#12
|
|||
|
|||
Thanks ron that works a treat
-- BD3 "Ron de Bruin" wrote: You can use a range like this Range("A6:G100") instead of UsedRange -- Regards Ron de Bruin http://www.rondebruin.nl "bigdaddy3" wrote in message ... ron ,yes in those tot 5 rows is information ie company name,address etc and are already coloured and i dont want those affected as they are on a template and give me a new sheet to be filled in every time that is why i need to be able to color below that down to row say 100. Both the sheet and workbook are locked in excel leaving just a certain no of cells to be filled in but if they are not thats why i want to color just the unfilled ones which can be different every time, i hope this makes sense -- BD3 "Ron de Bruin" wrote: When you use the code to protect you can use a password if you want Sh.Protect Password:="ron", userinterfaceonly:=True Hi Ron have done that okay but i would like to stop it coloring rows 1to 5 in columns a to p and then only down as far as row 100,also as far as the protected sheet goes it unprotects to Can you explain more ? -- Regards Ron de Bruin http://www.rondebruin.nl "bigdaddy3" wrote in message ... Hi Ron have done that okay but i would like to stop it coloring rows 1to 5 in columns a to p and then only down as far as row 100,also as far as the protected sheet goes it unprotects to fill but i would like it protected again when finished with the original password. any thoughts -- BD3 "Ron de Bruin" wrote: Hi Protect your sheets with code like this in the open event Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Each Sh In ThisWorkbook.Worksheets Sh.Protect userinterfaceonly:=True Next Application.ScreenUpdating = True End Sub and then when i do it even colors in some of my headings that are part of an initial template ive created. You can adapt the range sh.UsedRange -- Regards Ron de Bruin http://www.rondebruin.nl "bigdaddy3" wrote in message ... Hi ron, tried that but as the worksheet is protected it wont work unless i unprotect it and then when i do it even colors in some of my headings that are part of an initial template ive created. -- BD3 "Ron de Bruin" wrote: Hi BD3 You can run it in a event in the thisworkbook module This event will run when you save your workbook Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets On Error Resume Next sh.UsedRange.SpecialCells(xlCellTypeBlanks).Interi or.ColorIndex = 3 On Error GoTo 0 Next sh End Sub See this page about events http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "bigdaddy3" wrote in message ... Hi ron, where would i insert that code. -- BD3 "Ron de Bruin" wrote: Hi bigdaddy3 With code On Error Resume Next ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlank s).Interior.ColorIndex = 3 On Error GoTo 0 Mnual Select your data F5 Special..Blanks OK Choose a color in the formatting toolbar -- Regards Ron de Bruin http://www.rondebruin.nl "bigdaddy3" wrote in message ... i would like to automatically color all blank cells unfilled which can vary on each sheet at close of workbook,any suggestions .The total number of blank cells will be no more than 100 -- BD3 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup formula - treat no-registered cells as blank | Excel Worksheet Functions | |||
| Automatic changing refrences to sheet corresponding to first day. | Excel Discussion (Misc queries) | |||
| how to skip the blank cells | Excel Discussion (Misc queries) | |||
| copy blank cells | Excel Discussion (Misc queries) | |||
| copy blank cells | Excel Discussion (Misc queries) | |||
Threaded Mode
