Search column...change row to BOLD

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I'm an amateur excel user and I've just taught myself how to create a macro.
I have a bid program that I export a summary sheet to excel. I've set up a
macro to format column widths and column colors. I want to add to this macro
to search column A for WHOLE numbers. If the cell in column A is a whole
number, then I want to change that row to bold. Help please?
 
I took the time to clean up your recorded macro to remove selections, etc
Use this to bold your integer rows. To incorporate into the other use

boldintgers
as the last line before end sub in the merge_cells macro

Sub boldintegers()
mc = 1 'col A
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
mv = Cells(i, mc)
If Len(Application.Trim(mv)) > 0 _
And mv = Int(mv) Then
'MsgBox i
Rows(i).Font.Bold = True
End If
Next
End Sub

Sub Merge_Cells()
'I REALLY do NOT recommend MERGING CELLS. Use center across
Columns("C:D").Delete
Rows("2:2").Insert Shift:=xlDown
With Range("A1:A2,c1:d2,f1:m2")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("A").ColumnWidth = 7.57
Columns("B").ColumnWidth = 35.29
Columns("C").ColumnWidth = 12.14
Columns("D").ColumnWidth = 6.71
Columns("E").ColumnWidth = 10.43
Columns("F").ColumnWidth = 14
Columns("G").ColumnWidth = 11.43
Columns("H").ColumnWidth = 12.71
Columns("I").ColumnWidth = 11.71
Columns("J").ColumnWidth = 12
Columns("K").ColumnWidth = 15
Columns("L").ColumnWidth = 17.57
Columns("M").ColumnWidth = 13.14
Columns("N").ColumnWidth = 11.86
Columns("N").Interior.ColorIndex = 36
Columns("L").Interior.ColorIndex = 34

'REALLY slows things down so
'comment out or delete changes not necessary
'or UN comment what I commented
With ActiveSheet.PageSetup
'.PrintTitleRows = ""
'.PrintTitleColumns = ""
'.PrintArea = ""

'.LeftHeader = ""
'.CenterHeader = ""
'.RightHeader = ""
'.LeftFooter = ""
'.CenterFooter = ""
'.RightFooter = ""
'.LeftMargin = Application.InchesToPoints(0.75)
'.RightMargin = Application.InchesToPoints(0.75)
'.TopMargin = Application.InchesToPoints(1)
'.BottomMargin = Application.InchesToPoints(1)
'.HeaderMargin = Application.InchesToPoints(0.5)
'.FooterMargin = Application.InchesToPoints(0.5)
'.PrintHeadings = False
'.PrintGridlines = False
'.PrintComments = xlPrintNoComments
'.PrintQuality = 600
'.CenterHorizontally = False
'.CenterVertically = False

.Orientation = xlLandscape

'.Draft = False
.PaperSize = xlPaper11x17

'.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver

'.BlackAndWhite = False
'.Zoom = 100
'.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
 
Please forgive my ignorance! I REALLY appreciate your time and help more
than you know! I'm using merge as there is data in row 1 that won't fit in
the cell when I narrow the column width. For example, the text "Owned
Equipment Total Cost" (actually my column L in these examples) won't fit with
a column width of 17.57. So I merge the cell and wrap the text. Anyway,
onto the topic at hand....

I've made the changes you recommended and it DOES run a lot smoother!
However, now I'm having trouble with the boldintgers part. I get a "Run time
error '13':". Go to debug and "If Len(Application.Trim(mv)) >0>" is
highlighted yellow along with "And mv=Int(mv) Then" with an arrow to the
second line. Suggestions?

Mike
 
I did test.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.
 
Doug,

This is what I've been doing so far. But I could have 100 lines (depending
on the size of the particular project) and each line would have a varying
number of "subordinates". So I'm trying to set up a macro to search for the
integer numbers (1,2,3,...) and make that ROW bold. So I have for example a
pay item "8" Sewer Pipe" in Cell B15 (Cell A15 would be "1"). Cell A16 would
be 1.1 for "Buy materials". Cell B17 would be "Buy backfill material" (Cell
A17 would be 2.1). Cell A18 would be 2.1.1 for "Buy" in Cell B18 and Cell
A19 would be 2.1.2 for "Haul" in Cell B19. This data varies from one job to
the next. In this case, I want to highlight Row 15.
 
Mike,

The example I gave you will bold the entire ROW. I assumed that you were
always looking only at column A, which I still think is what you are saying,
but am not entirely sure. If it is multiple columns, it could still be
done.

hth,

Doug

Mike said:
Doug,

This is what I've been doing so far. But I could have 100 lines
(depending
on the size of the particular project) and each line would have a varying
number of "subordinates". So I'm trying to set up a macro to search for
the
integer numbers (1,2,3,...) and make that ROW bold. So I have for example
a
pay item "8" Sewer Pipe" in Cell B15 (Cell A15 would be "1"). Cell A16
would
be 1.1 for "Buy materials". Cell B17 would be "Buy backfill material"
(Cell
A17 would be 2.1). Cell A18 would be 2.1.1 for "Buy" in Cell B18 and Cell
A19 would be 2.1.2 for "Haul" in Cell B19. This data varies from one job
to
the next. In this case, I want to highlight Row 15.



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4168 (20090618) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
Click to expand...

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4168 (20090618) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
Don,
I found the problem. Your code works just fine. Here's the problem I'm
running into now. Column A may contain what EXCEL recognizes as something
other than a number. For example, it recognizes 2.1 as a number and the
counter works. When it comes across 2.1.1, the error occurs. I sent you a
file to your email. Not sure if you got it. I'm still working on it and
zeroing in on what I need! Thanks again for your help.

Mike
 
Back
Top