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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> > 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).Interior.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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > Hi ron, where would i insert that code.
> >> >> >> > --
> >> >> >> > BD3
> >> >> >> >
> >> >> >> >
> >> >> >> > "Ron de Bruin" wrote:
> >> >> >> >
> >> >> >> >> Hi bigdaddy3
> >> >> >> >>
> >> >> >> >> With code
> >> >> >> >>
> >> >> >> >> On Error Resume Next
> >> >> >> >> ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).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" <[email protected]> wrote in message
> >> >> >> >> news:[email protected]...
> >> >> >> >> >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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Bookmarks