Selecting some cells in a column for formatting?

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

I need a macro to select cells in a user input column, only in rows where
the contents of the cell in column AT (say) is X, then apply a set of
formatting commands to the selection. My VBA syntax and vocabulary are too
shaky for me to be confident of what should be an easy piece of code... Any
help gratefully received.

TIA
Dan
 
You don't say which cells to format, or what formatting. This code makes
column Z bold text.

Sub myRows()
Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count,"AT").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "AT").Value = "X" Then
If rng Is Nothing Then
Set rng = Cells(i,"Z")
Else
Set rng =Union(rng, Cells(i,"Z"))
End If
End If
Next i
If Not rng Is Nothing Then
rng.Font.Bold = True
End If

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
If you don't have to use VBA, you may want to look at Format|conditional
formatting.
 
Many thanks again, Bob (please see my Big thank-you in the "Sub gives
error..." posting - thread beginning 1/28)

Bob - in a number of workbooks - worksheets, I need to select a whole bunch
of non-continguous cells in a given column, then exit the macro and apply
various formatting commands to the selection - i.e. NOT apply the formatting
within the macro. Is there a simple way to run this sub, but end up with
all the selected cells still selected when the macro exits, so I can work on
the selection? Today, I'm going to buy a book on VBA / macros... I just
haven't found anthing on the web that teaches and is a decent cheat sheet.
So I may not be bugging you guys so often in future :-)

Cheers and TIA,

Dan
 
Dan E said:
Many thanks again, Bob (please see my Big thank-you in the "Sub gives
error..." posting - thread beginning 1/28)
Click to expand...

I did Dan, we all appreciated it.
Bob - in a number of workbooks - worksheets, I need to select a whole bunch
of non-continguous cells in a given column, then exit the macro and apply
various formatting commands to the selection - i.e. NOT apply the formatting
within the macro. Is there a simple way to run this sub, but end up with
all the selected cells still selected when the macro exits, so I can work on
the selection?
Click to expand...

Using my orinial code, just add a select at the end, like so

Sub myRows()
Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count,"AT").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "AT").Value = "X" Then
If rng Is Nothing Then
Set rng = Cells(i,"Z")
Else
Set rng =Union(rng, Cells(i,"Z"))
End If
End If
Next i
If Not rng Is Nothing Then
rng.Font.Bold = True
rng.Select
End If

End Sub
Today, I'm going to buy a book on VBA / macros... I just
haven't found anthing on the web that teaches and is a decent cheat sheet.
Click to expand...

Don't know what level you consider yourself at, but I would suggest, in
ascending order of development
Excel VBA Programming For Dummies - John Walkenbach
Excel 2003 Power Programming With VBA - JW again
Excel 2002 VBA Programmers Reference; John Green, Stephen Bullen, Rob Bovey,
Robert Rosenberg - (avoid the 2003 version , it has been messed up).
So I may not be bugging you guys so often in future :-)
Click to expand...

That's why we are here :-).
 
Once again, thank you so much. As far as programming skills level goes,
I've forgotten so much - used to program in AUTOCODE (Yes really - around
1959, at King's College, Newcastle - I was reading physics at Durham and
Newcastle was still part of Durham U. then), ALGOL, BASIC, FORTRAN - but all
so long ago... Then worked for the RN Scientific Service for 8 years after
my doctorate, then emigrated from England to Australia in '73 to a
university teaching/research position, then started a new department of
Computer Based Education (!) in 1985, retired in '96 with a health problem,
and moved to Tennessee 2 years ago. Most of my programming was probably as
a research student in the early '60s - all night sessions running my stuff
on the computer at Durham - ah me. Object oriented languages came along
afterwards. I've forgotten so much, but it's beginning to come back.

Thanks for the tips on books - much appreciated, and thanks for the coding
help, once again.

Dan
 
Dan E said:
Once again, thank you so much. As far as programming skills level goes,
I've forgotten so much - used to program in AUTOCODE (Yes really - around
1959, at King's College, Newcastle - I was reading physics at Durham and
Newcastle was still part of Durham U. then), ALGOL, BASIC, FORTRAN - but all
so long ago...
Click to expand...

I thought you were English from your original posts, but the Thanks post
suggested to me that the health authority you were working for was in the
US.

I started on Algol, never got on with it. Gave up programming after that,
and only started again when I came across Plan (ICL's version of assembler).
Then worked for the RN Scientific Service for 8 years after
my doctorate, then emigrated from England to Australia in '73 to a
university teaching/research position, then started a new department of
Computer Based Education (!) in 1985, retired in '96 with a health problem,
and moved to Tennessee 2 years ago.
Click to expand...

Didn't know it was possible to retire to the US. Don't the health costs make
that impractical?
Most of my programming was probably as
a research student in the early '60s - all night sessions running my stuff
on the computer at Durham - ah me. Object oriented languages came along
afterwards. I've forgotten so much, but it's beginning to come back.
Click to expand...

With your background, I think the Dummies book will run out of steam too
quickly. I would think that the Power Programming book is probably a better
start for you.

Regards

Bob
 
Thanks, Dave, but it's just too tricky to select all the cells manually -
there are merged cells in the way, and some cells that must not be selected.
The macro is really only to do the selecting. I'm beginning to discover the
advatges of recording a host of mouseclicks, then including that into the
meat of the macro, so I'm making some progess :-)

(Please see my Big thank-you in the "Sub gives error..." posting - thread
beginning 1/28)

Thanks again,

Dan
 
Well - a Tennessee lady and I fell for each other... Long story, and we got
married. She's Assistant Director of Nursing at a Health Care Facility
(which gives us group health insurance), and does the scheduling, so life
gets busy. We also run a real estate business.

Bob - how do I de-select the selected cells (rng) after this macro runs its
course? Sorry to be a nuisance.

I almost forgot - in Australia, when I was an academic in Physics Dept., I
got a PDP-11 up and running using RSX-something - the MINI-computer (!) was
like a huge washing machine with 5MB disk packs like small flying saucers -
I just read a yard of manuals after midnight - took me six weeks to figure
it all out. (We couldn't afford for DEC to come and install the software
for us...). Good fun.

Sub All_OT_Format()

ActiveSheet.Unprotect

Dim cLastRow As Integer
Dim i As Integer
Dim rng As Range

cLastRow = Cells(Rows.Count, "AT").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "AT").Value = "X" Then
If rng Is Nothing Then
Set rng = Cells(i, "BB")
Else
Set rng = Union(rng, Cells(i, "BB"))
End If
End If
Next i
If Not rng Is Nothing Then
rng.Select
Selection.NumberFormat = "General"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = xlNone
Selection.Locked = True
Selection.FormulaHidden = False

End If

ActiveSheet.Protect
End Sub
 
Dan E said:
Well - a Tennessee lady and I fell for each other... Long story, and we got
married. She's Assistant Director of Nursing at a Health Care Facility
(which gives us group health insurance), and does the scheduling, so life
gets busy. We also run a real estate business.
Click to expand...

Aah! Co0vered :-)
Bob - how do I de-select the selected cells (rng) after this macro runs its
course? Sorry to be a nuisance.
Click to expand...

Something has to be selected, so just select something else

Range("A1").Select
I almost forgot - in Australia, when I was an academic in Physics Dept., I
got a PDP-11 up and running using RSX-something - the MINI-computer (!) was
like a huge washing machine with 5MB disk packs like small flying saucers -
I just read a yard of manuals after midnight - took me six weeks to figure
it all out. (We couldn't afford for DEC to come and install the software
for us...). Good fun.
Click to expand...

Reminds me of a little tale a Dec salesman told me. He asked which was the
fastest computer in the world? So I said, Cray, Amdahl, some Big Blue model?
No, he said, it's a PDP-11. Get out of here said I, I'm not that green. Yes,
he says, there is one on the Voyager mission to Uranus :-).
 
Thanks, Bob - should have figured that out. Good story on the PDP!

Would you know of any web source that talks sensibly about ways of having a
user input a string, then passing the string as a parameter to a sub?

Dan
 
Dan, don't know of any web source but it is not difficult. Just trap the
return from InputBox and pass that, something like

ans = InputBox("Please supply the name")
If ans <> "" Then
mySub ans
Else
Msgbox "You must supply a value"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks, Bob. Bought two books - maybe return one when I've delved a
bit - -Bullen, Green et al 2002 VBA Prog. Ref., and Frye, Freeze and
Buckingham - MS Office Excel 2003 Programming Inside Out.

Both looked pretty good.

Thanks again,

Dan
 
Don't know about the second, but I can vouch for the first. I have the 2000
edition. My one and only Excel book, and it is a goodie.

Good Reading

Bob
 
Back
Top