Pre-formatting Cells to look blank until data is entered

Using Microsoft Excel 2003

Is it possible to pre-set formatting on cells so that the formatting on
those cells stay blank until data is entered, then the pre-set formatting is
autmatically applied?

For Example:  Instead of having the entire sheet with Border Formatting, and
having pages with blank data w/formatting, the pages will look blank until
data is entered, then the formatting will be applied.

Does that make sense?

Troy


0
Troy
12/22/2004 6:18:36 PM
excel 39879 articles. 2 followers. Follow

4 Replies
169 Views

Similar Articles

[PageSpeed] 6

Troy,

For example, you could use an event: this will copy the format of the cell
above after the entry has been made, for any single cell entry in columns A
or B:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:B")) Is Nothing Then
Application.EnableEvents = False
Target(0).Copy
Target.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub

Copy the code, right click on the sheet tab, select "View Code" and paste
the code into the window that appears.

HTH,
Bernie
MS Excel MVP

"Troy" <thesheriffsgun@hotmail*NOSPAm*.COMN> wrote in message
news:0Uiyd.12914$nN6.12362@edtnps84...
> Using Microsoft Excel 2003
>
> Is it possible to pre-set formatting on cells so that the formatting on
> those cells stay blank until data is entered, then the pre-set formatting
is
> autmatically applied?
>
> For Example:  Instead of having the entire sheet with Border Formatting,
and
> having pages with blank data w/formatting, the pages will look blank until
> data is entered, then the formatting will be applied.
>
> Does that make sense?
>
> Troy
>
>


0
Bernie
12/22/2004 6:35:48 PM
Thanks Bernie that works perfect,

Another Question.....Is there any way to copy the formatting for the entire
previous row into the current row.  Example. Your code will only format the
single cell when data is entered into it.  My question is this, if ANY cell
in the current ROW has data entered into it, is it possible to copy the
formatting from the entire previous ROW into ALL the cells in that current
ROW? (so that the even if there is no data in the one or two cells in that
row, the row will still look formatted)

I tried to look at the code to see if I could figure it out, but I guess I
don't understand coding that well.

Thanks again Bernie!








"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:e3J$OUF6EHA.2032@tk2msftngp13.phx.gbl...
> Troy,
>
> For example, you could use an event: this will copy the format of the cell
> above after the entry has been made, for any single cell entry in columns
A
> or B:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("A:B")) Is Nothing Then
> Application.EnableEvents = False
> Target(0).Copy
> Target.PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> Application.EnableEvents = True
> End If
> End Sub
>
> Copy the code, right click on the sheet tab, select "View Code" and paste
> the code into the window that appears.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Troy" <thesheriffsgun@hotmail*NOSPAm*.COMN> wrote in message
> news:0Uiyd.12914$nN6.12362@edtnps84...
> > Using Microsoft Excel 2003
> >
> > Is it possible to pre-set formatting on cells so that the formatting on
> > those cells stay blank until data is entered, then the pre-set
formatting
> is
> > autmatically applied?
> >
> > For Example:  Instead of having the entire sheet with Border Formatting,
> and
> > having pages with blank data w/formatting, the pages will look blank
until
> > data is entered, then the formatting will be applied.
> >
> > Does that make sense?
> >
> > Troy
> >
> >
>
>


0
Troy
12/22/2004 7:07:42 PM
Maybe something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    'should already be done if there's already a value
    'in one of the cells
    If Application.CountA(Target.EntireRow) > 1 Then Exit Sub    
    
    Application.EnableEvents = False
    Target(0).EntireRow.Copy
    Target.EntireRow.PasteSpecial xlPasteFormats
    Target.Select  'fix the selection
    Application.CutCopyMode = False
    Application.EnableEvents = True

End Sub




Troy wrote:
> 
> Thanks Bernie that works perfect,
> 
> Another Question.....Is there any way to copy the formatting for the entire
> previous row into the current row.  Example. Your code will only format the
> single cell when data is entered into it.  My question is this, if ANY cell
> in the current ROW has data entered into it, is it possible to copy the
> formatting from the entire previous ROW into ALL the cells in that current
> ROW? (so that the even if there is no data in the one or two cells in that
> row, the row will still look formatted)
> 
> I tried to look at the code to see if I could figure it out, but I guess I
> don't understand coding that well.
> 
> Thanks again Bernie!
> 
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:e3J$OUF6EHA.2032@tk2msftngp13.phx.gbl...
> > Troy,
> >
> > For example, you could use an event: this will copy the format of the cell
> > above after the entry has been made, for any single cell entry in columns
> A
> > or B:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Not Intersect(Target, Range("A:B")) Is Nothing Then
> > Application.EnableEvents = False
> > Target(0).Copy
> > Target.PasteSpecial xlPasteFormats
> > Application.CutCopyMode = False
> > Application.EnableEvents = True
> > End If
> > End Sub
> >
> > Copy the code, right click on the sheet tab, select "View Code" and paste
> > the code into the window that appears.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "Troy" <thesheriffsgun@hotmail*NOSPAm*.COMN> wrote in message
> > news:0Uiyd.12914$nN6.12362@edtnps84...
> > > Using Microsoft Excel 2003
> > >
> > > Is it possible to pre-set formatting on cells so that the formatting on
> > > those cells stay blank until data is entered, then the pre-set
> formatting
> > is
> > > autmatically applied?
> > >
> > > For Example:  Instead of having the entire sheet with Border Formatting,
> > and
> > > having pages with blank data w/formatting, the pages will look blank
> until
> > > data is entered, then the formatting will be applied.
> > >
> > > Does that make sense?
> > >
> > > Troy
> > >
> > >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
12/22/2004 9:10:31 PM
That works even better.  Thanks a lot for your help.  This is going to make
my document that much easier and nicer to work with.

Many Thanks!

Troy





"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41C9E2C7.A2884E47@netscapeXSPAM.com...
> Maybe something like this:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>     If Target.Cells.Count > 1 Then Exit Sub
>
>     'should already be done if there's already a value
>     'in one of the cells
>     If Application.CountA(Target.EntireRow) > 1 Then Exit Sub
>
>     Application.EnableEvents = False
>     Target(0).EntireRow.Copy
>     Target.EntireRow.PasteSpecial xlPasteFormats
>     Target.Select  'fix the selection
>     Application.CutCopyMode = False
>     Application.EnableEvents = True
>
> End Sub
>
>
>
>
> Troy wrote:
> >
> > Thanks Bernie that works perfect,
> >
> > Another Question.....Is there any way to copy the formatting for the
entire
> > previous row into the current row.  Example. Your code will only format
the
> > single cell when data is entered into it.  My question is this, if ANY
cell
> > in the current ROW has data entered into it, is it possible to copy the
> > formatting from the entire previous ROW into ALL the cells in that
current
> > ROW? (so that the even if there is no data in the one or two cells in
that
> > row, the row will still look formatted)
> >
> > I tried to look at the code to see if I could figure it out, but I guess
I
> > don't understand coding that well.
> >
> > Thanks again Bernie!
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:e3J$OUF6EHA.2032@tk2msftngp13.phx.gbl...
> > > Troy,
> > >
> > > For example, you could use an event: this will copy the format of the
cell
> > > above after the entry has been made, for any single cell entry in
columns
> > A
> > > or B:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count > 1 Then Exit Sub
> > > If Not Intersect(Target, Range("A:B")) Is Nothing Then
> > > Application.EnableEvents = False
> > > Target(0).Copy
> > > Target.PasteSpecial xlPasteFormats
> > > Application.CutCopyMode = False
> > > Application.EnableEvents = True
> > > End If
> > > End Sub
> > >
> > > Copy the code, right click on the sheet tab, select "View Code" and
paste
> > > the code into the window that appears.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "Troy" <thesheriffsgun@hotmail*NOSPAm*.COMN> wrote in message
> > > news:0Uiyd.12914$nN6.12362@edtnps84...
> > > > Using Microsoft Excel 2003
> > > >
> > > > Is it possible to pre-set formatting on cells so that the formatting
on
> > > > those cells stay blank until data is entered, then the pre-set
> > formatting
> > > is
> > > > autmatically applied?
> > > >
> > > > For Example:  Instead of having the entire sheet with Border
Formatting,
> > > and
> > > > having pages with blank data w/formatting, the pages will look blank
> > until
> > > > data is entered, then the formatting will be applied.
> > > >
> > > > Does that make sense?
> > > >
> > > > Troy
> > > >
> > > >
> > >
> > >
>
> --
>
> Dave Peterson


0
Troy
12/22/2004 10:11:01 PM
Reply:

Similar Artilces:

display content of merged cells
How can I display the content of merged cells in the adiacent empty cells? e.g. if I put some long unwrapped text in A1 I can see it in A2 and A3,as long as they are empty; but if I merge A1 and A2 i cannot see anymore the extra text in A3 even if empty. Thank you for any help! Luca Sounds like another good reason for not merging cells. The archives of this group have countless other reasons. -- David Biddulph LucaRR wrote: > How can I display the content of merged cells in the adiacent empty > cells? e.g. if I put some long unwrapped text in A1 I can see it in > ...

Formatting for Answers to Questions
I am sending out a questionnaire. Different people will provide different lengths of answers. How do I create a form that will expand if required. Use a text form field--you can even set the maximum length. More precise instructions would depend on what version of Word you're using. In Word 2007, you'd use a legacy text form field, which is available in the Developer tab (Word Options, Popular, Show Developer Tab). Use the Legacy Tools button to choose Text Form Field. This presumes that you're going to use this as a protected form in which the only place for text ent...

Excel VBA / Data Sorting / Maniputaltion
i have a long list, which i imported from other data base. The problem is: 1. In each cell, besides data (data is in alphabates) there are som numeric characters which i dont want . How can i get rid of all those unwanted numbers from data, from all th list? 2. The data is in every second row, ie. one row is empty. How can i Delete those empty/blank rows from the list -- Message posted from http://www.ExcelForum.com 1. I think we will need to see a sample of your data 2. Sort the data -- Message posted from http://www.ExcelForum.com I am sending the sample data file... ...

Sorting with merged cells in the worksheet
I've got a spreadsheet which has a section (A24:J42) that has six columns. Columns B-F have been merged in this data section, but not so in A1:J23. When I try sorting the data section I am only presented with columns (as a whole) to sort. Obviously, I'm getting the message "merged cells must be identical size". I've looked through past postings about this, and done some Internet searching, and the best I can find is how to unmerge the merged cells and the sort will work. Yup! It works just fine, but the problem is my data section in columns B-F contain...

Linking axes scale values to cells
For almost any type of chart the auto scale functions work fine. For OHLC stock charts however the auto scale always sets the minimum scale value to zero. If plotting a high priced stock, the chart is compressed into a small area at the top of the chart rendering it useless. If fixed values are used they must be constently reset as the stock price changes in order to avoid having the bars walk off the chart area. I can search the data with MAX(range) and MIN(range) in order to pick out the upper and lower limits dynamically. IS THERE ANY WAY TO LINK THE AXES SCALE VALUES TO THE CELL...

Can Excel email someone when a cell goes + or
So sort of like Conditional formatting where it changes colour - instead it adds rules to send an automatic email... Is this reaching for the stars? Start here Reload Internet http://www.rondebruin.nl/mail/change.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Reload Internet" <Reload Internet@discussions.microsoft.com> wrote in message news:489C49F6-1F69-42FF-BFD9-0986EE9D4AA8@microsoft.com... > So sort of like Conditional formatting where it changes colour - instead it > adds rules to send an automatic email... Is this reaching for the stars? I bet VBA...

delete cell that doesn't contain a specific word
I'm trying to delete a cell in excel if the content of the cell doesn't have a specific word. For example if I only wanted cells if it contained the word 'John' in it. Then the cell: I love excel - would be deleted but the cell: I love excel more than John - would not be deleted. I'm sure it's probably a simple solution but I'm not really sure how to do this. Any help would be much appreciated. Thanks Try this on a spare copy of the sheet Assuming the text cells are all in col A Select the range, then do a Data > Filter > Autofilter In the autofilter dr...

=(IF(ISTEXT('Data Report'!$L2:$L4),'Data Report'!N3,J11)) Response
I have 3 cells that may or may not contain text my statement is the following: =(IF(ISTEXT('Data Report'!$L2:$L4),'Data Report'!N3,J11)). What I need to happen is to have N3 posted in the cell of this formula if any of the three cells have text in them. I feel I am close but not quite there. The response I get is works only if all L2:L4 is FALSE,FALSE,FALSE or TRUE,TRUE, TRUE. Help is appreciated. Try: =if(or(istext($L2),istext($L3),istext($L4)),'Data Report!N3,J11) HTH, "s2frost" wrote: > I have 3 cells that may or may not contain text my stateme...

Seeing blank fields
In a report there is a field that has values in about half the records. I want the report to show either a bottom line border or a border around the whole field only when the field is blank. Can this be done? If so, how? Ed On Mon, 11 Feb 2008 06:01:48 -0500, "OldManEd" <econgleton@snet.net> wrote: >In a report there is a field that has values in about half the records. I >want the report to show either a bottom line border or a border around the >whole field only when the field is blank. Can this be done? If so, how? > >Ed Try something like this in ...

adding 1 to each following cells with a prefix
How do I automate the editing in situation such as the column content below. M001 M002 M003 There is prefix M00 but the cells of the rest is plus one from the first. If it's just numbers, I know =A1 + 1 and copy-and-paste would do it but it won't work with a prefix. Thanks for the help, Put M001 in your first cell, select that cell and then click on the small black square in the bottom right corner of that cell's border and drag it down as far as you want... Excel will automatically increment the number part for you. -- Rick (MVP - Excel) "cpliu" <spamfreeli...

Changing Calender Format
I am new to Outlook 2007. The bubbles around the appointments in the the on-screen month version of the day/week/month view consume a lot of space. at the default 8 point font, 3 appointments are the maximun that will show up on within the box for a day. The 2003 format (without bubbles) allowed more appointments to show for the same screen space. Does anyone know how to turn the bubbles off? ...

How can I paste a table of data into Excel from a MSIE / web browser?
Hi Is there any way to paste out of a table on a web page, *straight* into msExcel2002, but without taking microsoft all the friggin Micro$oft formatting with it. I just want the data, but I also want the table structure! Copying out of Firefox into Excel and table structure is lost - it just splurges out in a single line; and copying out of MSIE (latest) and either all sorts of formatting comes with it, or the table structure is lost. So at present I paste out of MSIE into Excel, save as tab-delimited text. Close. Re-open. Have to scroll down to find the correct format out of drop-down li...

Populating data from one CRM field to another. (CRM 3.0)
Hello, I have a custom field in Accounts. This field is called "discount" and it holds a number from 0-100. I have created a matching feild in Opportunities. When ever some one opens an Opportunity I want the discount field to be populated with the value from the discount field from the Account that the Opportunity belongs to. I have some ideas but I know I need a lot of help. For starters I figured I would use an OnLoad from Opportunities to check the Discount field from the Account and make opportunity discount field equal that value. crmForm.all.CFCRevSoftwareNet.DataValu...

Macro to Delete Blanks in Col and Move Remainder of Range Up
Win7 and Excel 2007 with ASAP Utilities Named range in a column with data filled by Paste-Special-Values and sorted A-Z by a Macro. All cells are text. Some cells are blank and sort to the top of the range. I would like to delete the blank cells and have the remaining values moved up but cannot write the required Macro. Any suggestions would be appreciated. Eric Try recording it and if you still can't get it post back with ALL of your code. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Zaidy036" <ericNOSPAM@bloch.com...

How to start the cell edit by keyboard
Hi everybody, I have the simple question about the excel. Let's suppose I have the cell with the text in excel worksheet. I need to edit this text inside the cell. Currently I have to double-click by the mouse to get the cursor into this cell to edit it. Please, is there any hotkey which enables to make the same by keyboard only. Thanks in advance Petr F2. "Petr Both" <testpboth@quick.cz> wrote in message news:OPpItSLwEHA.3416@TK2MSFTNGP09.phx.gbl... > Hi everybody, > > I have the simple question about the excel. Let's suppose I have the > cell with ...

MAXIMUM NUMBERS IN CELL
When entering more than 15 numbers in a cell, Exel automatically changes the integers after 15 to a zero. Example: 123456789123456789 entered into cell returns 123456789123456000. Is there any way around this? Thanx Excel supports only 15 digits, but if you don't want to do any calculations on these numbers, then you can enter them as strings. A preceding ' will convert numbers to string. e.g. '123456789123456789 is treated as a text string. - Mangesh -- Message posted via http://www.officekb.com ...

Need help with cells
I need help. I want to be able to type in a numberin cell A1 and the description to appear on cell B1. Just by typing the number. thank you Look in the Excel help for the Vlookup command. You will need to have a table of the numbers with their descriptions saved somewhere. "mariflor" <mariflor@discussions.microsoft.com> wrote in message news:9F284B62-4D22-4646-8BBD-0D1E7D586858@microsoft.com... >I need help. I want to be able to type in a numberin cell A1 and the > description to appear on cell B1. Just by typing the number. > thank you I can't seem to ge...

Data is lost...XP
I got excel file from mail.This file use second excel file to get some data . When I open excel file that I got, I can see data but XP ask me do you want tu update or not ,When I select one of them,some of my data is lost ... On the orther side ,When try same thing in office97 , ask me yes no, When I select yes or no,I can see data in excel file I got from mail. What is problem in XP ? I need help this is a bug ????????????????????????? ...

USING EDIT FIND to locate cell references
If I put various cell references inside a formula, is there an easy wa to find the reference without going into each and every cell. Fo example if cell C6 is "=IF(ISERROR(T9/Q9),"",T9/Q9)" and I want to fin all references to T9 can I somehow convert cell C6 to be say the text o the formula and then do a Edit Find on all cells in the worksheet? I hope that i explained myself. Thanks -- Ron Wilso ----------------------------------------------------------------------- Ron Wilson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2729 View this thre...

Formula help, compare amount in two cells
Thanks in advance for any help: I want to compare the amounts in two separate columns. If the amount in cell c4 is less than the amount in cell c6, then subtract c4 from c6 and put the result in c5 and color it red. I know this is simple but I just can't get it to work. Any help is really appreciated. In C5 put =IF(C4<C6,C6-C4,0) Then click on C5 and do Format > Conditional Format > Formula Is > =$C$5>0 and select Format > Patterns > and select Red color > OK > OK Vaya con Dios, Chuck, CABGx3 "29kilo" <anonymous@discussions.microsoft.com...

Using cell value in range reference
I have a value of 20 in cell A1, how can I use this value in a formula. E.g. COUNTA("D11:D" & A1) - (I feel it should be similar to this ) Sandy =COUNTA(INDIRECT("D11:D"&a1)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message news:4011B2E9-7F93-44E8-A3DF-96746B31C7FD@microsoft.com... >I have a value of 20 in cell A1, how can I use this value in a formula. > > E.g. COUNTA("D11:D" & A1) - (I feel it should be similar to this ) > > ...

Calendar and Data Entry
Hi all, I need to build a dynamic calendar (by month) for employees (changing) in which they will enter different codes (Vacation, Illness, Training, etc). Can anyone please help on getting started? Is MS Access the best tool? Thanks a lot. JF There are literally dozens of them. You can start with mine which is an Access form that works (with conversion as required) in any version of Access) http://www.datastrat.com/Download/Calendar2K.zip There are some on Stephen Lebans site: http://www.lebans.com/monthcalendar.htm and the Access Web: http://www.mvps.org/access/forms/frm0052.h...

Pulling data into Extender Window
Hi, We recently set up an Entender window in the AR Customer Master to capture additional information that we need for reporting. The information is currently in several of the GP Fields such as Country Code, Comment2, etc. We would like to somehow integrate the information that is currently in those fields into the Extender Fields so that we don't have to re-key information on over 1,000 customers. Is there a way to do this?? To do it within GP, you would need to export the data and then import it using the Extender import tool. To do it outside of GP would require the followi...

cells within cell
In an existing worksheet I have cells hemmed in, not able to add additional rows or columns. I need 4 more cells in the column but do not have room to add them. Is there a way to click on a single cell that pops-up a mini-column of 4 cells? I would want numbers inserted in the pop-cells to be summed into the original cell. An example would be if I clicked on B2, a mini-column with four cells would pop-up. When I add numbers to the pop-up mini-column, those numbers would sum in B2. Regards, Gary You can't have a "mini-column" but one workaround might be: Insert a secon...

Get column of named cell
I have a named cell - StaffHeader1 - I want to refernce just the Column property of this cell in another piece of vba - how do I do that ? So in my code I am going to do this; iColumn = Column(StaffHeader1) Sheet6.Cells(3, iColumn + 1).Value = iStaffName Any help appreciated Thanks Check your other post. Isis wrote: > > I have a named cell - StaffHeader1 - I want to refernce just the Column > property of this cell in another piece of vba - how do I do that ? > > So in my code I am going to do this; > iColumn = Column(StaffHeader1) > Sheet6.Cells(...