Pre-formatting Cells to look blank until data is entered

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

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
 
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
 
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!
 
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
 
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
 
Back
Top