On Jun 15, 2:04 pm, "ekkehard.horner"
wrote:
Highlander schrieb:



On Jun 14, 1:53 pm, urkec wrote:
"Highlander" wrote:

objExcel.ActiveSheet.Range("B:B").Select
objExcel.Selection.NumberFormat = "#,##0"
No need to select the column:

objExcel.ActiveSheet.Columns("B:B").NumberFormat = "#,##0"

--
urkec

That pares it down to just one line. Thanks urkec.

While we're on a roll, I have another question. I want to implement
conditional formatting; where if a cell value in Column C is greater
than 50000, then that cell font will be changed to bold. Recording a
macro gave me this:

Sub Bold_Column()
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="50000"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
End Sub

I've tried transposing the code from this macro into my script, with
no luck. Any suggestions would be greatly appreciated.

Thanks!

- Dave

From your

Const xlDescending = 2
Const xlNormal = 1
Const xlCenter = -4108

code I gather, you'll have no problems to add the constants
for xlCellValue and xlGreater. That leaves you with the task to
convert the VBA named parameters like "Type:=xlCellValue" to
VBScript positional ones: Use the Excel/VBA documentation
to determine the prototype of the FormatConditions.Add method;
if you are lucky the method's parameter list looks like

Add Type, Operator, Formula1, Formula2

then you can write

Selection.FormatConditions.Add xlCellValue, xlGreater, "50000"

otherwise you'll have to distribute your values to the correct
positions. E.g.

Add Operator, Formula2, Type, Formula1

==

Selection.FormatConditions.Add xlGreater, , xlCellValue, "50000"

(use , , to skip (optional) parameters you can't/won't set)- Hide quoted text -

- Show quoted text -

Okay I've added these Constants:

Const xlCellValue = 1
Const xlGreater = 5

And then the following code works:

'~~ Insert Bold and Red Font for any value greater than 50000 in
Column C
objExcel.Range("C:C").Select
objExcel.Selection.FormatConditions.Delete
objExcel.Selection.FormatConditions.Add xlCellValue, xlGreater,
"50000"
With objExcel.Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3 'Red
.Italic = False
End With

There is one problem though. Some of the cells in Column C contain
text, not a number, and the text is getting changed to Bold and Red
font. I only want the font to change for cells that contain a number.
How do I exclude the text cells from the conditional formatting?

Thanks in advance!

- Dave