How to set font size using code 2

[ruthcali]

I am using Excel 2000.

Can I create a button to automatically set some formatting changes to a spreadsheet?

I'm trying to set the font size, printer and gridlines.

I would also like to change the font color using an IF statement (if cell value like "*closed*" then Cell value.FontColor =red.).

I'm new to programming with Excel.

Thanks!

 

[DaleWatson123321]

Hi ruthcali,

To set the font size..
Selection.Font.Size = 12

To turn the GridLines on...
ActiveSheet.PageSetup.PrintGridlines = True

Re: I would also like to change the font color using an IF statement (if cell value like "*closed*" then Cell value.FontColor =red.)...

I would suggest using "Conditional Formatting" on the cell(s) where "closed" will be entered.

Steps:
1) From the menu: Format - Condition Formatting
2) Cell Value Is ... equal to ... ="closed"
3) Select "Format"
4) Select the "Color" tab
5) Click "Color", and choose Red.

For other printer settings, you should consider recording a macro...
1) From the menu: Tools - Macro - Record New Macro
2) Use Page - Setup and choose your printer settings.
3) Then use <Al> <F8> to view and edit the recorded macro - to adjust those recorded settings required.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ruthcali]

Dale,
thanks for writing back so quickly!

I want to make the button on one tab and have it change the data in another tab.

So to tell excel what tab i mean, do i first need to type something like:

With Worksheets(&quot;qryExample&quot.Activate
Selection.Font.Size = 12
ActiveSheet.PageSetup.PrintGridlines = True


For the conditional formatting, i am trying to say: IF column B = 'closed' then the whole row turns red.

So if cell B1=closed, then make A1, B1, C1, D1, E1, F1...all turn red.

Or if cell B3=closed, then A3,B3,C3,D3... all turn red.

The conditional formatting will change one cell, but not the whole row.

The macro for the printer is a great idea!

 

[DaleWatson123321]

I'll get back to you later with a macro for printing.

For the Conditional Formatting...

Steps:

1) In Cell A1... Format - Conditional Formatting

2) Under &quot;Condition 1&quot;, click the Down-Arrow, and choose &quot;Formula Is&quot;

3) In the adjacent box, enter this formula:
=$B1=&quot;closed&quot;

4) Click &quot;Format&quot; and format as desired (Red text).

5) After exiting the Conditional Format window, copy Cell A1 to all the cells and rows where you require it. This can include ALL columns and ALL rows if required.

6) Naturally, however, if you already have data in other cells, you do NOT want to over-write the data. Therefore, (after copying cell A1, and highlighting your destination range, use: Edit - Paste Special - Formats. Potentially, this could cause a problem if you have &quot;different&quot; number formats in your destination range. In such a case, you will then have to re-format the destination cells that require a different number format. However the &quot;Conditional Formatting&quot; that you copied will remain.

I hope you are able to follow the above. Let me know if you have any difficulty.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ruthcali]

Thanks again for writing!
The formatting works perfectly!!!! i didn't know you could put a formula there. great. thanks!

Does Excel have a &quot;*like*&quot; function like Access does? Some of the Excel cells just say 'closed', but some say 'closed project' or 'project closed'. And i want all those cells to turn red as well.


For the font and the gridlines, can you tell me what to type before:

Selection.Font.Size = 12
ActiveSheet.PageSetup.PrintGridlines = True

I tried: With Worksheets(&quot;qryExample&quot.Activate,
but it didn't work.

I can do the macro for the printer. I first have to install the printer on my machine since it's a plotter.

Thanks again.

 

[DaleWatson123321]

I've been busy, so I'll have to get back later with the print macro. Or perhaps other Tek-Tips members will jump in to help out.

But regarding the Conditional Formatting... I don't believe Excel has a &quot;like&quot; function. However, here's an easy solution for your situation...

Do the same as before. But instead use the following formula...

=OR($B1=&quot;closed&quot;,$B1=&quot;closed project&quot;,$B1=&quot;project closed&quot

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[petrosky]

Hi ruthcali,

Here is an example of the print macro that Dale mentioned you can of course use the macro recorder to set the configuration you desire and assign the macro to a command button.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/3/03 by Peter Wright
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = &quot;&quot;
.PrintTitleColumns = &quot;&quot;
End With
ActiveSheet.PageSetup.PrintArea = &quot;&quot;
With ActiveSheet.PageSetup
.LeftHeader = &quot;&quot;
.CenterHeader = &quot;&quot;
.RightHeader = &quot;&quot;
.LeftFooter = &quot;&quot;
.CenterFooter = &quot;&quot;
.RightFooter = &quot;&quot;
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
End Sub


Regards,

Peter Remember- It's nice to be important,
but it's important to be nice

 

[ruthcali]

Hi,
wow. thanks to both of you!!

For the font and the gridlines, can you tell me what to type before:

Selection.Font.Size = 12
ActiveSheet.PageSetup.PrintGridlines = True

I tried: With Worksheets(&quot;qryExample&quot.Activate,
but it didn't work.

i'm writing this code on a tab i called 'code' and i want it to change all the data in the tab called 'qryExample'.

Thanks,

 

[petrosky]

Hi again,

I think this should do what you want.

Sub SetFont()
'
' SetFont Macro
' Macro recorded 14/3/03 by Peter Wright
'


Range(&quot;A2&quot.Select
Application.Goto Reference:=&quot;qryExample&quot;
With Selection.Font
.Name = &quot;Arial&quot;
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.PageSetup.PrintGridlines = True

End Sub

You simply copy/paste the above into a new module, then to run you could create a command button or a custom menu button.

To create the custom bar you simply right click anywhere on the toolbar/menubar area and choose customise, select the commands tab, scroll down to Macros and drag the item call &quot;Custom Menu Item&quot; to the menu bar somewhere, right click on it and choose assign macro.
Select the macro Setfont and you should be sweet.

Regards,

Peter Remember- It's nice to be important,
but it's important to be nice

 

[ruthcali]

Peter,
It works perfectly!! Thank you!! And i made my own custom button like you advised above. it works great!

What is Range(&quot;A2&quot.Select? I played around and changed the A2 to different values, and each time the code still worked. Just wondering.

I wonder if i can ask another question?
I have 13 columns of data and about 400 rows.

We have a big plotter where all the data can fit on one large sheet of paper, but only if the data is divided into 2 separate sets of columns.

So i need my data divided into: A1 thru M200 and then O2 thru AA200.

In other words, any data below row 200 should be moved and placed in columns O thru AA.

I found some code that works for data in one column (A) only. i'm trying to tweak it to work with more than one column, but since i don't understand the code, it hasn't been successful yet.
The webpage where i found it is:

http://www.ozgrid.com/VBA/MiscVBA.htm

Here's a handy little bit of code if you have a long list of data in 1 Column (&quot;A&quot; in this case). It will automatically move the range of data at each horizontal page break to adjacent columns.
Sub RowsToColumns()
Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim i As Integer, iPBcount As Integer
Dim Sht As Worksheet

Application.StatusBar = &quot;Converting, please wait....!&quot;
Application.ScreenUpdating = False

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea = &quot;&quot;
Sht.PageSetup.Zoom = 100
ActiveWindow.View = xlPageBreakPreview

'Count only horizontal page breaks and pass to an Integer
iPBcount = Sht.HPageBreaks.Count

On Error Resume Next
'Loop as many times as there horizontal page breaks.
For i = 1 To iPBcount
'Set variable 1 to page break X
Set rCell1 = Sht.HPageBreaks(i).Location
'Set variable 2 to X page break
Set rCell2 = Sht.HPageBreaks(i + 1).Location.Offset(-1, 0)
If rCell2 Is Nothing Then 'Last page break
Range(rCell1, rCol.Cells(65536, 1).End(xlUp)).Cut _
Destination:=Sht.Cells(1, i + 1)
Else
Range(rCell1, rCell2).Cut Destination:=Sht.Cells(1, i + 1)
End If
Set rCell1 = Nothing
Set rCell2 = Nothing
Next i
On Error GoTo 0

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
Sht.DisplayPageBreaks = False
Application.Goto rCol.Cells(1, 1), True
Set rCol = Nothing
Application.StatusBar = &quot;&quot;
End Sub


I think i'll start a new thread since this is different than my original question.