VBScript Excel formatcondition cell: syntax

[DrDre]

Hi there,

I'am working on a VBScript that fills a Excel-sheet with data from
text-files

The following code should create condition formating within a cell:

Wiith oWorkbook.cells(Regel, cColBGCount+dicbg.Count+1)
.FormulaR1C1 = "=COUNTA(RC[-7]:RC[-1])-SUM(RC[-7]:RC[-1])"
.FormatConditions.Delete
.FormatConditions.Add xlCellValue, xlEqual, "0"
.FormatConditions.Add xlCellValue, xlLess, "-1"
.FormatConditions(1).Interior.ColorIndex = vbYellow
.FormatConditions.Add xlCellValue, xlLess, "-2"
.FormatConditions(2).Interior.ColorIndex = vbRed
End With

The syntax of the fourth line I got from Internet an it works, but the
next line:
.FormatConditions(1).Interior.ColorIndex = vbYellow
generates a syntax-error (translate from dutch to english):
property colorindex of class Interior can not be set (?!?!?)
(dutch: eigenschap colorindex van klasse Interior kan niet worden
ingesteld)

If I am not clear, just ask me..

TIA,

André vWG

 

[Bob Phillips]

vbYellow is a colour constant, not a colour INDEX constant.

try

.FormatConditions(1).Interior.Color = vbYellow

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi there,

I'am working on a VBScript that fills a Excel-sheet with data from
text-files

The following code should create condition formating within a cell:

Wiith oWorkbook.cells(Regel, cColBGCount+dicbg.Count+1)
.FormulaR1C1 = "=COUNTA(RC[-7]:RC[-1])-SUM(RC[-7]:RC[-1])"
.FormatConditions.Delete
.FormatConditions.Add xlCellValue, xlEqual, "0"
.FormatConditions.Add xlCellValue, xlLess, "-1"
.FormatConditions(1).Interior.ColorIndex = vbYellow
.FormatConditions.Add xlCellValue, xlLess, "-2"
.FormatConditions(2).Interior.ColorIndex = vbRed
End With

The syntax of the fourth line I got from Internet an it works, but the
next line:
.FormatConditions(1).Interior.ColorIndex = vbYellow
generates a syntax-error (translate from dutch to english):
property colorindex of class Interior can not be set (?!?!?)
(dutch: eigenschap colorindex van klasse Interior kan niet worden
ingesteld)

If I am not clear, just ask me..

TIA,

André vWG

 

[DrDre]

Thanks Bob,

vbBob=vbThanks

You are/were correct: it works

André