Automatic coloring of blank cells when sheet work complete

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

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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Hi thanks ron ill do that
--
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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
>> >>
>> >>
>> >>
>>
>>
>>

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
> >>
> >>
> >>
>
>
>

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
>>
>>
>>

Hi Hayeso, i tried that but as the sheet and workbook are protected it brings
up the dialog box "unable to set the color prop of interior class"
--
BD3


"Hayeso" wrote:

> Select Tools...Macro...Visual Basic Editor
> Select View...Project Explorer (If Project Explorer not already visible)
> Expand the VBAProject for your workbook and double click on the ThisWorkbook
> icon
>
> Place the following code in the code window (RHS)
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim aRange As Range, aCell As Range, aSheet As Worksheet
> Dim aColour As Single
> aColour = vbRed
> For Each aSheet In ThisWorkbook.Sheets
> Set aRange = aSheet.UsedRange
> If aRange.Cells.Count > 1 Then
> For Each aCell In aRange
>
> If aCell = "" Then
> aCell.Interior.Color = aColour
> End If
> Next
> End If
> Next
> End Sub
>
>
> Select File...Close and return to Microsoft Excel
>
> Close the workbook and save when asked.
>
>
> "bigdaddy3" wrote:
>
> > 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

Select Tools...Macro...Visual Basic Editor
Select View...Project Explorer (If Project Explorer not already visible)
Expand the VBAProject for your workbook and double click on the ThisWorkbook
icon

Place the following code in the code window (RHS)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim aRange As Range, aCell As Range, aSheet As Worksheet
Dim aColour As Single
aColour = vbRed
For Each aSheet In ThisWorkbook.Sheets
Set aRange = aSheet.UsedRange
If aRange.Cells.Count > 1 Then
For Each aCell In aRange

If aCell = "" Then
aCell.Interior.Color = aColour
End If
Next
End If
Next
End Sub


Select File...Close and return to Microsoft Excel

Close the workbook and save when asked.


"bigdaddy3" wrote:

> 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

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
>
>
>

thanks i will try that
--
BD3


"Hayeso" wrote:

> Select Tools...Macro...Visual Basic Editor
> Select View...Project Explorer (If Project Explorer not already visible)
> Expand the VBAProject for your workbook and double click on the ThisWorkbook
> icon
>
> Place the following code in the code window (RHS)
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim aRange As Range, aCell As Range, aSheet As Worksheet
> Dim aColour As Single
> aColour = vbRed
> For Each aSheet In ThisWorkbook.Sheets
> Set aRange = aSheet.UsedRange
> If aRange.Cells.Count > 1 Then
> For Each aCell In aRange
>
> If aCell = "" Then
> aCell.Interior.Color = aColour
> End If
> Next
> End If
> Next
> End Sub
>
>
> Select File...Close and return to Microsoft Excel
>
> Close the workbook and save when asked.
>
>
> "bigdaddy3" wrote:
>
> > 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

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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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

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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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

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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Select Tools...Macro...Visual Basic Editor
Select View...Project Explorer (If Project Explorer not already visible)
Expand the VBAProject for your workbook and double click on the ThisWorkbook
icon

Place the following code in the code window (RHS)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim aRange As Range, aCell As Range, aSheet As Worksheet
Dim aColour As Single
aColour = vbRed
For Each aSheet In ThisWorkbook.Sheets
Set aRange = aSheet.UsedRange
If aRange.Cells.Count > 1 Then
For Each aCell In aRange

If aCell = "" Then
aCell.Interior.Color = aColour
End If
Next
End If
Next
End Sub


Select File...Close and return to Microsoft Excel

Close the workbook and save when asked.


"bigdaddy3" wrote:

> 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

Hi thanks ron ill do that
--
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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

thanks i will try that
--
BD3


"Hayeso" wrote:

> Select Tools...Macro...Visual Basic Editor
> Select View...Project Explorer (If Project Explorer not already visible)
> Expand the VBAProject for your workbook and double click on the ThisWorkbook
> icon
>
> Place the following code in the code window (RHS)
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim aRange As Range, aCell As Range, aSheet As Worksheet
> Dim aColour As Single
> aColour = vbRed
> For Each aSheet In ThisWorkbook.Sheets
> Set aRange = aSheet.UsedRange
> If aRange.Cells.Count > 1 Then
> For Each aCell In aRange
>
> If aCell = "" Then
> aCell.Interior.Color = aColour
> End If
> Next
> End If
> Next
> End Sub
>
>
> Select File...Close and return to Microsoft Excel
>
> Close the workbook and save when asked.
>
>
> "bigdaddy3" wrote:
>
> > 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

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
>> >>
>> >>
>> >>
>>
>>
>>

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
> >>
> >>
> >>
>
>
>

Hi Hayeso, i tried that but as the sheet and workbook are protected it brings
up the dialog box "unable to set the color prop of interior class"
--
BD3


"Hayeso" wrote:

> Select Tools...Macro...Visual Basic Editor
> Select View...Project Explorer (If Project Explorer not already visible)
> Expand the VBAProject for your workbook and double click on the ThisWorkbook
> icon
>
> Place the following code in the code window (RHS)
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim aRange As Range, aCell As Range, aSheet As Worksheet
> Dim aColour As Single
> aColour = vbRed
> For Each aSheet In ThisWorkbook.Sheets
> Set aRange = aSheet.UsedRange
> If aRange.Cells.Count > 1 Then
> For Each aCell In aRange
>
> If aCell = "" Then
> aCell.Interior.Color = aColour
> End If
> Next
> End If
> Next
> End Sub
>
>
> Select File...Close and return to Microsoft Excel
>
> Close the workbook and save when asked.
>
>
> "bigdaddy3" wrote:
>
> > 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

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
>>
>>
>>

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
>
>
>