Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel - Autoformat (Entire Row)

Posted on 1999-06-22
13
Medium Priority
?
935 Views
Last Modified: 2008-02-20
I would like to Autoformat an entire row in excel based on the contents of a single cell within that row... Any ideas on how to do this w/o using VBA scripts or macros?
0
Comment
Question by:Kallik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 23

Expert Comment

by:ahammar
ID: 1606937
Kallik,
You can format the one cell and then drag that cell across as many columns as you want. As far as autoformatting based on one cell, I don't think you can do that without a macro of some kind. A macro to do this would be very short and simple.

Sub FormatRow
     ActiveCell.EntireRow.NumberFormat = Activecell.NumberFormat
End Sub

That would format the entire row of the active cell to the same as the format of the active cell. It could alse be easily changed to look at any cell you wanted and format based on that cell.

If you would like a step by step instuctions on how to put that in a macro, I'll tell you. Just let me know. It's easy.
ahammar
0
 
LVL 23

Expert Comment

by:ahammar
ID: 1606938
Hi again,
Ok, I think I jumped the gun here, that doesn't work.
I'll work on it though.
Sorry about that.
ahammar
0
 
LVL 1

Author Comment

by:Kallik
ID: 1606939
I was thinking more along the lines of selecting an entire sheet and formating the rows within based upon the contents of a single column meeting my criteria.  Imagine the Autoformat wizard allowing you to format the selected cell based on the criteria of another cell.  For example i have a summary worksheet where each row within the worksheet contains customer information, one column within that worksheet contains delinquency information, i would like all rows (customers) that meet a certain delinquency criteria to be highlighted in yellow.  Currently i do this by using autofilter and setting the criteria on the delinquency column, then i simply highlite all the filtered rows and format them.  Unfortunately i have to repeat the filter process every time information in the delinquency column changes, i was just thinking how much simpler life would be if i could use Autoformat.  Also i don't really have anything against macro's I just hate building up this nifty little library of tricks and then feeling like a useless idiot when i'm using anyones computer but my own.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:ahammar
ID: 1606940
Hi again Kallik,
I guess I really lied on this one. It would be kinda long. I know you said you wanted to do this without a macro, but just in case you don't get a better answer, and you decide to use a macro, this one will format the entire row of the active cell to match that of the active cell.
If you are already sure you don't want to use a macro, then just ignore this.
Cheers!
ahammar
ps...It's still easy to put it in a module


Sub FormatRow()
       ActiveCell.EntireRow.Select
    With Selection
        .Locked = ActiveCell.Locked
        .Locked = ActiveCell.FormulaHidden
        .Borders(xlDiagonalDown).LineStyle = ActiveCell.Borders(xlDiagonalDown).LineStyle
        .Borders(xlDiagonalDown).LineStyle = ActiveCell.Borders(xlDiagonalUp).LineStyle
        .NumberFormat = ActiveCell.NumberFormat
        .HorizontalAlignment = ActiveCell.HorizontalAlignment
        .VerticalAlignment = ActiveCell.VerticalAlignment
        .WrapText = ActiveCell.WrapText
        .Orientation = ActiveCell.Orientation
        .ShrinkToFit = ActiveCell.ShrinkToFit
        .MergeCells = ActiveCell.MergeCells
    End With
   
    With Selection.Font
        .Name = ActiveCell.Font.Name
        .FontStyle = ActiveCell.Font.FontStyle
        .Size = ActiveCell.Font.Size
        .Strikethrough = ActiveCell.Font.Strikethrough
        .Superscript = ActiveCell.Font.Superscript
        .Subscript = ActiveCell.Font.Subscript
        .OutlineFont = ActiveCell.Font.OutlineFont
        .Shadow = ActiveCell.Font.Shadow
        .Underline = ActiveCell.Font.Underline
        .ColorIndex = ActiveCell.Font.ColorIndex
    End With
   
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = ActiveCell.Borders(xlEdgeLeft).LineStyle
        .Weight = ActiveCell.Borders(xlEdgeLeft).Weight
        .ColorIndex = ActiveCell.Borders(xlEdgeLeft).ColorIndex
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = ActiveCell.Borders(xlEdgeTop).LineStyle
        .Weight = ActiveCell.Borders(xlEdgeTop).Weight
        .ColorIndex = ActiveCell.Borders(xlEdgeTop).ColorIndex
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = ActiveCell.Borders(xlEdgeBottom).LineStyle
        .Weight = ActiveCell.Borders(xlEdgeBottom).Weight
        .ColorIndex = ActiveCell.Borders(xlEdgeBottom).ColorIndex
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = ActiveCell.Borders(xlEdgeRight).LineStyle
        .Weight = ActiveCell.Borders(xlEdgeRight).Weight
        .ColorIndex = ActiveCell.Borders(xlEdgeRight).ColorIndex
    End With
    With Selection.Interior
        .ColorIndex = ActiveCell.Interior.ColorIndex
        .Pattern = ActiveCell.Interior.Pattern
        .PatternColorIndex = ActiveCell.Interior.PatternColorIndex
    End With

End Sub

0
 
LVL 23

Expert Comment

by:ahammar
ID: 1606941
Hi again,
I posted before I read your last comment. That would be nice to have a feature like you say. Also, it's not your fault Excel can't do certain things, so no need to feel like an idiot. I could be wrong, but I think you will have to use a macro.
Would you like me to write one that will do what you want?
ahammar

0
 
LVL 23

Expert Comment

by:ahammar
ID: 1606942
Hi again, I'll post anyway since I've been working on it. This is a very rough draft and will probably need to be modified to fit your needs, but it's a start.
The above still applies, if your not interested in using a macro, then just ignore this.
This procedure will ask you which column you want to use. Then it will ask you for the criteria. As it is now, the only criteria choice you have must be a number.
It will search down every row in the column you specified starting at row 1 and continuing until the first blank cell in that column. If it finds a cell that matches the criteria, it will format that entire row to match the formatting of Range M1.If you want to try it, put some bogus data an a sheet,(remember for now that the column you sort by has to be a number). Then format M1 however you want and run this macro.
Cheers!
ahammar

Sub FormatRow()
        Dim SrtColmn As String
        Dim r As Range
        Dim Criteria As Integer
       
         
       SrtColmn = InputBox("Which column do you want to use as criteria range? Letter only please.")
       If SrtColmn = "" Then Exit Sub
       
       SrtColmn = SrtColmn & "1" '
         Set r = Range(SrtColmn)
       Criteria = InputBox("Number to look for?")
 
 Do Until r = ""
    If r.Value = Criteria Then
 
       
       r.EntireRow.Select
    With Selection
        .Locked = Range("M1").Locked
        .Locked = Range("M1").FormulaHidden
        .Borders(xlDiagonalDown).LineStyle = Range("M1").Borders(xlDiagonalDown).LineStyle
        .Borders(xlDiagonalDown).LineStyle = Range("M1").Borders(xlDiagonalUp).LineStyle
        .NumberFormat = Range("M1").NumberFormat
        .HorizontalAlignment = Range("M1").HorizontalAlignment
        .VerticalAlignment = Range("M1").VerticalAlignment
        .WrapText = Range("M1").WrapText
        .Orientation = Range("M1").Orientation
        .ShrinkToFit = Range("M1").ShrinkToFit
        .MergeCells = Range("M1").MergeCells
    End With
   
    With Selection.Font
        .Name = Range("M1").Font.Name
        .FontStyle = Range("M1").Font.FontStyle
        .Size = Range("M1").Font.Size
        .Strikethrough = Range("M1").Font.Strikethrough
        .Superscript = Range("M1").Font.Superscript
        .Subscript = Range("M1").Font.Subscript
        .OutlineFont = Range("M1").Font.OutlineFont
        .Shadow = Range("M1").Font.Shadow
        .Underline = Range("M1").Font.Underline
        .ColorIndex = Range("M1").Font.ColorIndex
    End With
   
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = Range("M1").Borders(xlEdgeLeft).LineStyle
        .Weight = Range("M1").Borders(xlEdgeLeft).Weight
        .ColorIndex = Range("M1").Borders(xlEdgeLeft).ColorIndex
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = Range("M1").Borders(xlEdgeTop).LineStyle
        .Weight = Range("M1").Borders(xlEdgeTop).Weight
        .ColorIndex = Range("M1").Borders(xlEdgeTop).ColorIndex
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = Range("M1").Borders(xlEdgeBottom).LineStyle
        .Weight = Range("M1").Borders(xlEdgeBottom).Weight
        .ColorIndex = Range("M1").Borders(xlEdgeBottom).ColorIndex
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = Range("M1").Borders(xlEdgeRight).LineStyle
        .Weight = Range("M1").Borders(xlEdgeRight).Weight
        .ColorIndex = Range("M1").Borders(xlEdgeRight).ColorIndex
    End With
    With Selection.Interior
        .ColorIndex = Range("M1").Interior.ColorIndex
        .Pattern = Range("M1").Interior.Pattern
        .PatternColorIndex = Range("M1").Interior.PatternColorIndex
    End With
End If
Set r = r.Offset(1, 0)
Loop

End Sub

0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1606943
What about this way (it shorter):
Sub FormatRow2()
  LastRow = ActiveSheet.UsedRange.Rows.Count
  LastCol = ActiveSheet.UsedRange.Columns.Count
  Application.ScreenUpdating = False
  ActiveCell.Copy
  For Each c In Range(Cells(1, ActiveCell.Column), Cells(LastRow, ActiveCell.Column))
    If IsEmpty(c) Then
       Exit For
    End If
    If c.Value = ActiveCell.Value Then
      Set tRange = Range(Cells(c.Row, 1), Cells(c.Row, LastCol))
      tRange.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
   Next
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
End Sub
0
 
LVL 22

Expert Comment

by:ture
ID: 1606944
Kallik,

I hope that I have understood what you want to achieve.

Here is a simple non-macro way to make the entire row yellow if the value in the A column of that row is 5.

1. Select the entire sheet
2. Format - Conditional Formatting
3. Enter this for Condition1:
   Formula is    =$A1=5
4. Click "Format" - Patterns tab - Yellow - OK - OK

Ture Magnusson
Karlstad, Sweden
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1606945
ture - it's for Excel97, isn't it? :-)
For Excel95 - only with VBA, I think.
Kallik - about "building up this nifty little library of tricks " - VBA module is ever in same workbook - so if you bring your file to another PC, your macros will be with you too.
0
 
LVL 22

Expert Comment

by:ture
ID: 1606946
vboukhar,

Yes - Conditional Formatting was introduced in Excel 97, so my suggested solution only works in Excel 97 and Excel 2000.

Ture Magnusson
Karlstad, Sweden
0
 
LVL 1

Author Comment

by:Kallik
ID: 1606947
ahammer, i would love to give you the points for all your hard work, but Ture hit this one right on the head.  Many thanks to everyone for their excellent suggestions.

p.s. - Ture please post an answer.
0
 
LVL 22

Accepted Solution

by:
ture earned 400 total points
ID: 1606948
Kallik,

I am glad that I was able to solve your problem. Here is my solution again:

Here is a simple non-macro way to make the entire row yellow if the value in the A column of that row is 5.

1. Select the entire sheet
2. Format - Conditional Formatting
3. Enter this for Condition1:
   Formula is    =$A1=5
4. Click "Format" - Patterns tab - Yellow - OK - OK

Ture Magnusson
Karlstad, Sweden
0
 
LVL 1

Author Comment

by:Kallik
ID: 1606949
thanks again Ture.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question