Syntax for Excel formatting 2

[BasicBoy]

I wish to colour the background of cell in an Excel sheet a certain colour and I am using the following syntax :

Set moApp = New Excel.Application
Set moWB = moApp.Workbooks.Add
i=10:j=10
moWB.Sheets("Sheet1").Cells(i, j).FormatConditions(1).Interior.ColorIndex = 35

What could be wrong?

Thanks

 

[Swi]

Easiest way to find out syntax in Excel would be to simply record a macro in Excel and take a look at the code. Thanks.

Swi

 

[BasicBoy]

I have tried this but it gives me an error on the first line

moWB.Sheets("Sheet1").Range(j, i).Select
With Selection.Interior
.Color = 65535
End With

Thanks

 

[Swi]

What values to j and i hold?

Thanks.

Swi

 

[Swi]

Something like this should work. Thanks.

' Add a reference to Microsoft Excel X.X Object Library
Option Explicit
Dim Xcl As Excel.Application
Dim newbook As Excel.Workbook

Private Sub Command1_Click()
    Set Xcl = New Excel.Application
    Xcl.Visible = False
    Xcl.DisplayAlerts = False
    Xcl.SheetsInNewWorkbook = 1
    Set newbook = Xcl.Workbooks.Add
    newbook.Worksheets(1).Range("A5:D17").Interior.Color = 65535
    newbook.Worksheets(1).Range("A5:D17").Interior.Pattern = xlSolid
    newbook.SaveAs "C:\Users\Delp Family\Desktop\test\Test.xls"
    newbook.Close
    Set newbook = Nothing
    Xcl.Quit
    Set Xcl = Nothing
    MsgBox "Done!", vbInformation + vbOKOnly
End Sub

Swi

 

[Swi]

Also, keep in mind the difference between Color and Color Index.

Choosing Between ColorIndex and Color

Swi

 

[Tomeczek]

Go to "Developer" tab, click on "Design Mode"
From "ActiveX Controls", insert a button on the spreadsheet called RunBtn.
Double-click on it and add following code (within "Private Sub RunBtn_Click() ... and ...End Sub")

Private Sub RunBtn_Click()
Dim n As Long

Range("A3").Activate

For n = 1 To 56
    ActiveCell.Offset(n, 0).Interior.ColorIndex = n
    ActiveCell.Offset(n, 1).Font.ColorIndex = n
    ActiveCell.Offset(n, 1).Value = "Color Index " & n
Next n

End Sub

Exit "Design Mode" and click on the button.
You will see all colors you can use with .ColorIndex.

Hope you will like it!

 

[BasicBoy]

Thanks for your good contributions.
Can you tell me how I change the name of a worksheet?

 

[Tomeczek]

1. You can right click on the tab (bottom of the sheet) and select "Rename".

2. In the VBA code: ActiveSheet.Name = "NewName"

 

[BasicBoy]

I am in VB6 - would that be the same?

 

[Tomeczek]

Yes, it will be the same.
You can also use:

Worksheets("Sheet1").Name = "Some Name

 

[strongm]

>You can also use:
>
>Worksheets("Sheet1").Name = "Some Name"

Just to be pedantic, this only works if a) the sheet has not already been renamed and b) Excel is running under the English language. In French, for example, "Sheet1" would be "Feuil1"

 

[Andrzejek]

To be even more pedantic...

>this only works if a) the sheet has not already been renamed

You can rename a sheet as many times as you want, as long as you do not use the name of the sheet that already exists in this workbook.

Is that what you meant, strongm?

Have fun.

---- Andy

 

[BasicBoy]

Can I not use worksheets(1).name="myname" ?
I tried this but it did not work.

 

[SkipVought]

but it did not work

???

What does that mean? You got an error and nothing got named? your computer exploded? your personal name got changed, but the sheet did not?

worksheets(1).name="myname"

1) you need an application reference, unless you are programming in the Excel VB Editor, which ASSUMES the Excel Application Object, and such questions ought to be posted in forum707.

2) you already have an Excel sheet named myname? Can't have more than one sheet with the same name.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

@andrejezak

No

 

[Tomeczek]

Here are examples of different actions in VB6 when working with Excel:

...
    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add

' if you want to delete last two of three sheets:
    Set oSheet = oBook.Worksheets.Item(3)
    oSheet.Delete
    Set oSheet = oBook.Worksheets.Item(2)
    oSheet.Delete

' if you want to rename Sheet1:
    Set oSheet = oBook.Worksheets.Item(1)
    oSheet.Name = "New Sheet Name"

' to activate sheet:
    oSheet.Activate

' to make Excel visible:
    oXL.Visible = True
    oXL.UserControl = True
' (if you don't want to expose Excel while manipulating it, set both to "False")

' to activate cell "A1":
    oSheet.Range("A1").Activate

...